Inserting multiple rows in MS Access 2007 using SQL

chonabraham
chonabraham used Ask the Experts™
on
Is it possible to insert multiple rows or records into a table at the same time using Insert Statements such as:
INSERT INTO track (trknum, trktitle, trklength) VALUES (1, 'Giant steps', 4.72);
INSERT INTO track (trknum, trktitle, trklength) VALUES (2, ‘Cousin Mary’, 5.75);
INSERT INTO track (trknum, trktitle, trklength) VALUES (3, ‘Countdown’, 2.35);
INSERT INTO track (trknum, trktitle, trklength) VALUES (4, ‘Spiral’, 5.93);
INSERT INTO track (trknum, trktitle, trklength) VALUES (5, “Syeeda’s song flute”, 7);
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
if you don't have the row stored some where you can use union select to do one insert statement

INSERT INTO track (trknum, trktitle, trklength)
(Select 1, 'Giant steps', 4.72
Union
Select 2, ‘Cousin Mary’, 5.75
Union
Select 3, ‘Countdown’, 2.35
Union
Select 4, ‘Spiral’, 5.93
Union
Select 5, “Syeeda’s song flute”, 7
)

Author

Commented:
I tried this and get a syntax error at the first  (Select.... shoudl there be a comma or something after the trklenght) and before the (Select  ?

The table has an autonumber as the Primary Key which I do not want to add in manually. So the 1, 2, 3 etc are just data for an attribute in the table. I'm not sure if that makes a difference in how the syntax should be written.

INSERT INTO track (trknum, trktitle, trklength)
(Select 1, 'Giant steps', 4.72
Union
Select 2, ‘Cousin Mary’, 5.75
Union
Select 3, ‘Countdown’, 2.35
Union
Select 4, ‘Spiral’, 5.93
Union
Select 5, “Syeeda’s song flute”, 7
)
Top Expert 2010
Commented:
Hello chonabraham,

I think Waseems very nearly had it.  Try:

INSERT INTO track (trknum, trktitle, trklength)
Select 1, 'Giant steps', 4.72
Union
Select 2, 'Cousin Mary', 5.75
Union
Select 3, 'Countdown', 2.35
Union
Select 4, 'Spiral', 5.93
Union
Select 5, 'Syeeda''s song flute', 7


If that doesn't work...



INSERT INTO track (trknum, trktitle, trklength)
SELECT z.f1, z.f2, z.f3
FROM (
Select 1, 'Giant steps', 4.72
Union
Select 2, 'Cousin Mary', 5.75
Union
Select 3, 'Countdown', 2.35
Union
Select 4, 'Spiral', 5.93
Union
Select 5, 'Syeeda''s song flute', 7 AS f3) AS z




Regards,

Patrick
Commented:
Unlike T-SQL it seems you can not run multiple INSERT statements at once.

I also came to a similar problem as you although I was trying to run a single query as shown in the two comments above.
It seems this is also not possible though as MS Access requires a FROM clause for each SELECT statement.

The way I managed to get around the issue was to use VBA to simply run multiple Inserts one after another.

code:
currentDB.Execute("INSERT INTO track (trknum, trktitle, trklength) VALUES (1, 'Giant steps', 4.72)")
currentDB.Execute("INSERT INTO track (trknum, trktitle, trklength) VALUES (2, ‘Cousin Mary’, 5.75)")
currentDB.Execute("IINSERT INTO track (trknum, trktitle, trklength) VALUES (3, ‘Countdown’, 2.35)")
currentDB.Execute("INSERT INTO track (trknum, trktitle, trklength) VALUES (4, ‘Spiral’, 5.93)")
currentDB.Execute("INSERT INTO track (trknum, trktitle, trklength) VALUES (5, “Syeeda’s song flute”, 7)")

Not too sure about your case in particular but for mine I was able to use one line within a for loop that could then access arrays holding each of the values for each new record.

Hope this helps

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial