Acces - insert multiple rows in a single sql-insert

Acces - insert multiple rows in a single sql-insert

Post by TUBydGl » Tue, 27 Jan 2009 22:52:01


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
 
 
 

Acces - insert multiple rows in a single sql-insert

Post by Danny Lesa » Tue, 27 Jan 2009 22:57:19

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'

--
Danny J Lesandrini
XXXX@XXXXX.COM
www.amazecreations.com

 
 
 

Acces - insert multiple rows in a single sql-insert

Post by TUBydGl » Fri, 20 Feb 2009 19:23:02

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?
 
 
 

Acces - insert multiple rows in a single sql-insert

Post by TUBydGl » Fri, 20 Feb 2009 19:27:01

Correction:

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

Acces - insert multiple rows in a single sql-insert

Post by Brendan Re » Fri, 20 Feb 2009 19:52:01


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

--
Brendan Reynolds
 
 
 

Acces - insert multiple rows in a single sql-insert

Post by TUBydGl » Fri, 20 Feb 2009 20:06:19

Thank you Brendan,

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


/Martin
 
 
 

Acces - insert multiple rows in a single sql-insert

Post by Rick Brand » Fri, 20 Feb 2009 21:31:27


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.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
 
 
 

Acces - insert multiple rows in a single sql-insert

Post by TUBydGl » Fri, 20 Feb 2009 22:40:05

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