Acces - insert multiple rows in a single sql-insert

[M@rtin]

Hi!

I would like to know if it's possible (in Access 2007) to make a single
"insert into" in a table, with multiple rows specified ?
Something like:
INERT INTO MYTABLE VALUES('1','2','3'),('4','5','6'),('7','8','9');

I couldn't make it work in Access 2003, and it's very annoying since my
program now is making INSERT INTO in a loop, with single rows, which is VERY
time consuming when the number of loops are large. It works fine in
MSSQL-server.

Thanks,

Martin

 

[Danny Lesandrini]

You can do this, but you need to use a UNION SELECT instead of VALUES ...


INSERT INTO MyTable (fld02, fld02, fld03)
SELECT '1','2','3'
UNION SELECT '4','5','6'
UNION SELECT '7','8','9'

 

[M@rtin]

Thanks for your reply, but have you tried this yourself?

This works:
INSERT INTO MyTable (fld1,fld2,fld03)
SELECT '1','2','3'

But this doesn't:
INSERT INTO MyTable (fld1, fld2, fld3, fld4, fld5)
SELECT '1','2','3'
UNION SELECT '4','5','6'
UNION SELECT '7','8','9'

The error I get is:
Syntax error (missing operator) in query expression...

The problem occurs after the first SELECT. Access seems to be unable to
interprete the rest of the query. Solutions anyone?

 

[M@rtin]

Correction:

Thanks for your reply, but have you tried this yourself?

 

[Brendan Reynolds]

Correction:

Thanks for your reply, but have you tried this yourself?

As far as I can tell (and I haven't looked into this in any great detail, so
I could very easily be wrong) JET SQL appears to require a FROM clause in
this context. Try something like this ...

INSERT INTO MyTable (fld1, fld2, fld3)
SELECT '1','2','3' FROM SomeTable
UNION SELECT '4','5','6' FROM SomeTable
UNION SELECT '7','8','9' FROM SomeTable

 

[M@rtin]

Thank you Brendan,

The problem is that I'm not retrieving those values from any tables, so I
can't use "FROM"...


/Martin

 

[Rick Brandt]

Thank you Brendan,

The problem is that I'm not retrieving those values from any tables, so
I can't use "FROM"...

Just pick any table and it should work. Might be better to choose a
smaller one. Normally that would produce a row of your literals per row
in the table used in the FROM, but the UNION will eliminate duplicates.

 

[M@rtin]

Sorry, still no progress.

Works:
INSERT INTO MyTable (fld1, fld2, fld3)
SELECT '1','2','3' FROM FILE

Doesn't work:
INSERT INTO MyTable (fld1, fld2, fld3)
SELECT '1','2','3' FROM FILE
UNION SELECT '4','5','6' FROM FILE

Error: Syntax error in FROM clause

To me it seems more and more like a multiple-row-insert is impossible in MS
Access 2k3... at least without fetching existing values from tables. Crap! Am
I really the first developer trying to insert multiple rows with values from
variables instead of tables??? Double-crap!

/Martin

 

[Blessed Tabvirwa]

Because this works:
INSERT INTO MyTable (fld1, fld2, fld3)
SELECT '1','2','3' FROM FILE

You can do:
INSERT INTO MyTable (fld1, fld2, fld3)
SELECT *
FROM (select top 1 "b1a" AS fld1, "b2a" AS fld2, "b3a" AS fld3 from dummytable
union all
select top 1 "b1b" AS fld1, "b2b" AS fld2, "b3b" AS fld3 from dummytable
union all
select top 1 "b1c" AS fld1, "b2c" AS fld2, "b3c" AS fld3 from dummytable)

BUT you need to make sure of the following:
1) The dummy table "dummytable" MUST already exist in your access DB
2) The dummy table "dummytable" MUST have at least 1 row of data
3) The "top 1" ensures you don't get repeats for a table with more than one row


But again, it might be easier just to do separate inserts, especially if you are already building things up in a loop.