Search code examples
sqlms-accessmultirowinsert-into

Multirow insert into statement in MS Access

I'm an SQL newbie and trying to figure out how to insert multiple table entries in one SQL statement. Here is my code:

INSERT INTO [Students](P_Id,FirstName,LastName,class,city,Phone)
SELECT 123,'Avi','Davis',2,'Tel-Mond','03-456789'
UNION
SELECT 234, 'Dani',2,'Dimona',' 02-111'
UNION 
SELECT 345,'Itzik',3,'Ariel', '03-2222'
UNION 
SELECT456, 'Koby', 3, 'Tel-Aviv', '03-333333'
UNION 
SELECT 789,'Moshe' ,2 , 'Tel-Aviv','03-7777777'

I've tried all sorts of variations on the theme with "help" from various tutorials, but nothing I've tried works so far. It seems like with each different DB program SQL usage differs slightly.

Any suggestions as to how to change my code so that it will work with MS Access?

Solution

  • You can do something like that in MS Access, but you must have a from table, and you must take care that only one row is returned:

    INSERT INTO [Students](P_Id,FirstName, LastName, class,  city,Phone)
SELECT 123 As P_ID, 'Avi' As FirstName, 'Davis' As LastName, 2 As Class,
        'Tel-Mond' As City,'03-456789' As Phone FROM AnyTable
UNION
<...>

    It can be easier to use VBA and loop.