Acces - insert multiple rows in a single sql-insert

  • Thread starter Thread starter M@rtin
  • Start date Start date
M

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
 
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'
 
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 said:
Correction:

Thanks for your reply, but have you tried this yourself?
Click to expand...


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
 
Thank you Brendan,

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


/Martin
 
Thank you Brendan,

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

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.
 
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
 
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.
 
Back
Top