2

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?

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
Meir
  • 12,285
  • 19
  • 58
  • 70
  • After your first select statement, you started omiting the lastname value. – JeffO Aug 18 '10 at 18:31
  • 1
    One is tempted to ask "Why?" You can jump through hoops to get Access to do this, but it would clearer to do it as several INSERTs in a row. – Larry Lustig Sep 27 '11 at 15:49

3 Answers3

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.

BIBD
  • 15,107
  • 25
  • 85
  • 137
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • You can ensure only one row is returned by including "top 1" in the select statements. See http://stackoverflow.com/questions/62504/ for an example. You may want to create a dummy/utility table to do tricks like this (like Oracle's DUAL table), rather than mixing it up with a table that contains "real" data. – BIBD Aug 18 '10 at 18:10
  • UNION will do it, too. UNION is unique values, UNION ALL is more than one of a kind. – Fionnuala Aug 18 '10 at 18:51
1

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.

Alan Macdonald
  • 1,872
  • 20
  • 36
0

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.

MikeAinOz
  • 126
  • 1
  • 10
  • 24
  • While it's true that Jet/ACE is restricted to one SQL statement at a time, the original question lists only one SQL statement (INSERT INTO...SELECT UNION...), so I'm not sure what your point is. – David-W-Fenton Aug 19 '10 at 17:40
  • Pardon me for not being clearer. Typically multi-row inserts are generated by multiple insert statements in a script. Access has a real weakness here in that it can't execute a multi-statement SQL script. Hence my comment that it is easier to use an SQL client to perform scripts rather than trying to work around it in Access. – MikeAinOz Aug 21 '10 at 23:10
  • Access/Jet/ACE is what it is -- a file-based database engine. If you've chosen it, you shouldn't be complaining about the things it can't do, as that was part of the choice you made. – David-W-Fenton Sep 30 '11 at 19:57