Insert Into command just works for one Value not for more values because of comma placement

Question

Tried to fill in a table with insert into command, but it just works for one value not for more.

I work with MS Access and the mistake is the comma placement, but I didn't find the mistake. Tried it with just one value and that works, but I have to insert it all.

INSERT INTO Abteilung (ID, Abteilung, Mitarbeiteranzahl)
VALUES (('1', 'Einkauf', '5'), ('2', 'HR', '5'), ('3', 'Controlling', '5'),
        ('4', 'Produktion', '20'), ('5', 'Vertrieb', '20'),
        ('6', 'Qualitätsmanagement', '3'), ('7', 'Industrial Engineering', '8')
       )

You can't insert more than 1 row with an INSERT statement. You must use multiple insert statements but not as a single transaction. — forpas, Nov 28 '19 at 19:05

score 1 · Answer 1 · answered Nov 28 '19 at 19:32

You can't do this as easily in access as other major db but it can be made possible. First off, create yourself a table with one row:

CREATE TABLE dual([Dummy] Integer)

INSERT INTO dual VALUES(1)

Now you can select a bunch of hard coded values "from" this table:

INSERT INTO Abteilung 
(
    ID, 
    Abteilung, 
    Mitarbeiteranzahl
) 
SELECT '1' as e1,'Einkauf' as e2, '5' as e3 FROM dual
UNION  
SELECT  '2','HR','5'  FROM dual
UNION  
SELECT  '3','Controlling','5' FROM dual 
UNION  
SELECT  '4','Produktion','20' FROM dual 
UNION  
SELECT  '5','Vertrieb','20'  FROM dual
UNION  
SELECT  '6','Qulaitätsmanagement','3' FROM dual 
UNION  
SELECT  '7','Industrial Engineering','8' FROM dual

You might need to wrap all those selects in another SELECT * FROM, I can't quite remember

By the time you're done writing all those out you might well get to thinking it would be as easy to just write N number of insert statements..

score 0 · Answer 2 · answered Nov 28 '19 at 19:05

0

There is a way how you can insert rows without building SQL strings.

check this Answer

answered Nov 28 '19 at 19:05

Krish

5,917
2
14
35

score 0 · Answer 3 · answered Nov 28 '19 at 19:30

You can't insert more than 1 row with an INSERT statement in Access.
Even multiple INSERT statements separated with ; are not allowed.
You can use a trick though like this:

INSERT INTO Abteilung ( ID, Abteilung, Mitarbeiteranzahl) 
SELECT * FROM (
  SELECT '1' AS ID, 'Einkauf' AS Abteilung, '5' AS Mitarbeiteranzahl FROM (SELECT COUNT(*) FROM Abteilung)  
  UNION ALL 
  SELECT  '2','HR','5'  FROM (SELECT COUNT(*) FROM Abteilung) 
  UNION ALL 
  SELECT  '3','Controlling','5'  FROM (SELECT COUNT(*) FROM Abteilung) 
  UNION ALL 
  SELECT  '4','Produktion','20'  FROM (SELECT COUNT(*) FROM Abteilung) 
  UNION ALL 
  SELECT  '5','Vertrieb','20'  FROM (SELECT COUNT(*) FROM Abteilung) 
  UNION ALL 
  SELECT  '6','Qulaitätsmanagement','3'  FROM (SELECT COUNT(*) FROM Abteilung) 
  UNION ALL 
  SELECT  '7','Industrial Engineering','8'  FROM (SELECT COUNT(*) FROM Abteilung)
)

Unfortunately the FROM clause is needed for each of the INSERTs and it must return only 1 row, this is why I used SELECT COUNT(*) which for a large table may be not that efficient.

Thanks that worked. I just learned that you can use this Insert Into command for more than one value if you do it like that : INSERT INTO Abteilung VALUES ((4,"test","na"),(5,"test2","ka")) — LUKAS, Nov 28 '19 at 19:36
@LUKAS this: `INSERT INTO Abteilung VALUES ((4,"test","na"),(5,"test2","ka"))` does not work in Access. Did you try it? — forpas, Nov 28 '19 at 20:00

score -2 · Answer 4 · edited Nov 28 '19 at 19:19

-2

Can you try the following query:

INSERT INTO Abteilung 
(
    ID, 
    Abteilung, 
    Mitarbeiteranzahl
) 
SELECT '1','Einkauf','5' 
UNION ALL SELECT  '2','HR','5'  
UNION ALL SELECT  '3','Controlling','5'  
UNION ALL SELECT  '4','Produktion','20'  
UNION ALL SELECT  '5','Vertrieb','20'  
UNION ALL SELECT  '6','Qulaitätsmanagement','3'  
UNION ALL SELECT  '7','Industrial Engineering','8'

Hope this should work.

edited Nov 28 '19 at 19:19

hc_dev

8,389
1
26
38

answered Nov 28 '19 at 19:11

Ramkumar Kangeyan

1
2

Did you test your query before posting? Good start! Keep improving your answer; maybe using the given hint: [multiple insert statements](https://stackoverflow.com/questions/62504/is-there-any-way-to-create-multiple-insert-statements-in-a-ms-access-query) ! – hc_dev Nov 28 '19 at 19:24
Still just works if i put in the first SELECT but with the next UNION ALL SELECT it won't work anymore, because access is missing an operator for the rest – LUKAS Nov 28 '19 at 19:28
Yes I have tested in ms sql – Ramkumar Kangeyan Nov 28 '19 at 19:29
Access does not allow you to `UNION` multiple `SELECT` statements unless each of them includes a `FROM` clause. – HansUp Nov 28 '19 at 20:05

Insert Into command just works for one Value not for more values because of comma placement

4 Answers4