Insert SQL for multiple record

Question

I get the error message when I run the following query in MSSQL Server 2005.

Error Message is Incorrect syntax near ','. I think query is ok. But I don't know why I get error.

INSERT INTO PERSON (ID, EMP_NAME) VALUES ('E001', 'AAA'), ('E002', 'BBB');

SQL Server does not support?

I think multiple inserts like that are supported in and above MSSQL Server 2008 — Zyga, Jul 26 '13 at 07:33
Inserting multiple values with that syntax is introduced with SQL Server 2008. You have to go with `UNION ALL` in 2005. http://stackoverflow.com/questions/3272487/sql-server-2005-insert-multiple-rows-with-single-query — Nenad Zivkovic, Jul 26 '13 at 07:33

score 6 · Accepted Answer · answered Jul 26 '13 at 07:31

6

If your DB is lower than SQL Server 2008

INSERT INTO PERSON (ID, EMP_NAME) VALUES ('E001', 'AAA');
INSERT INTO PERSON (ID, EMP_NAME) VALUES ('E002', 'BBB');

answered Jul 26 '13 at 07:31

Robert

Omg, really? I thought that every database server support simple sql query like that. Thz for your providing. – Zaw Than oo Jul 26 '13 at 07:33
@Parado, thz for message `If your DB is lower than SQL Server 2008` – Zaw Than oo Jul 26 '13 at 07:45
Why did you delete [this](http://stackoverflow.com/a/17879677/458741) @Parado, it's correct. If you undelete I'd upvote. – Ben Jul 26 '13 at 11:05
@Ben Undeleted, but I'm looking for last and first position of char – Robert Jul 26 '13 at 11:08
@Ben Now I see it wasn't needed. – Robert Jul 26 '13 at 11:17

score 4 · Answer 2 · answered Jul 26 '13 at 07:31

4

Try to use UNION ALL -

INSERT INTO Person (id, EMP_NAME)
SELECT id = 'E001', EMP_NAME = 'AAA'
UNION ALL 
SELECT 'E002', 'BBB'

answered Jul 26 '13 at 07:31

Devart

Yes, I also use like that. Alternatively, I just change the syntax. – Zaw Than oo Jul 26 '13 at 07:35

score 3 · Answer 3 · answered Jul 26 '13 at 11:47

3

Try this:

INSERT INTO Person (id, EMP_NAME)
SELECT 'E001', 'AAA'
UNION ALL 
SELECT 'E002', 'BBB'

answered Jul 26 '13 at 11:47

kabijoy

3 Answers3