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:

开发者_StackOverflow中文版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?

---

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.

---

I wanted to expand on the answers given here because today I tried the multi line insert approach in order to increase performance in our application and DID NOT get any noticable improvement.

I got it working by using an approach similar to Remou's above but you probably wanted UNION ALL otherwise two identical rows will not be inserted as UNION has an implicit distinct and I seemed to need an outer select that was aliased otherwise it didn't work. Additionally When you union as mentioned above you need a from table in Access so I followed the Oracle convention and created a single row table called DUAL.

INSERT INTO [Students](P_Id,FirstName, LastName, class,  city,Phone)
SELECT * FROM
(
 SELECT 123 As P_ID, 'Avi' As FirstName, 'Davis' As LastName, 2 As Class,
        'Tel-Mond' As City,'03-456789' As Phone FROM DUAL
UNION ALL
 SELECT 456 As P_ID, 'FDA' As FirstName, 'RET' As LastName, 3 As Class,
        'lima' As City,'03-456789' As Phone FROM DUAL
.
.
.
.
) as MyAlias

In my example I made it more simple and created a single column table with a varchar(50) column. I tried this with 1000 rows and Access complained the "query is too complex". I had to take it down to 49 rows to get it to insert successfully. This suggests you would need to batch up your inserts into smaller chunks for Access to accept it.

As a result there was no increase in performance. It's not worth the hassle and requires the batch up logic in code so really for me means I will be looking elsewhere for performance gains.

---

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.