asked on

Inserting multiple rows in MS Access 2007 using SQL

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);

Waseems

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
)

chonabraham

ASKER

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
)

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

IT101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial