Multirow insert into statement in MS Access

Question

Hi,

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?

Answer 1

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.

Answer 2

Access will only run one SQL statement in a query. Normally in Access you would load data from a csv file or spreadsheet. If you really want to do it in SQL get a client like iSQLviewer which will connect to most databases (I haven't tried it with Access) and will run scripts.