Insert several values in SQL Server

Question

I'm trying to insert some values into SQL Server but I get an error message:

Line 2: Incorrect syntax near ','

I need to enter several values in 1 table so I was looking for a quicker way to not insert 1 at a time.

For some reason I always have issues with databases :(

My query is:

INSERT INTO PERSONALRH_NIVEL (SERHGCCAB, PUESTO_ID, COMPANIA_ID, REGION_ID, TIPO_EMPLEADO)
VALUES (81570, 4, 2001, 2, 'N'), 
       (81570, 4, 2001, 3, 'S'), 
       (81570, 4, 2001, 3, 'N');

Thank you in advance, David

Your syntax looks fine. What version of SQL Server are you using? — Gordon Linoff
– Gordon Linoff, Commented Jun 24, 2016 at 19:13
See the accepted answer here stackoverflow.com/questions/22235531/…. — Jim Hewitt
– Jim Hewitt, Commented Jun 24, 2016 at 19:14
@JimHewitt . . . That question is for MySQL and is not the best method for SQL Server. — Gordon Linoff
– Gordon Linoff, Commented Jun 24, 2016 at 19:15
This was introduced in SQL Server 2008 - so if you have an older version, or if you database is in a compatibility level of an older database, then this syntax will cause errors. — marc_s
– marc_s, Commented Jun 24, 2016 at 19:18
Do a SELECT @@Version from within SSMS for server version. For DB compatability level it's SELECT compatibility_level FROM sys.databases WHERE name = 'MyDatabase'; DB Compatability level matrix is here: msdn.microsoft.com/en-us/library/bb510680.aspx — squillman
– squillman, Commented Jun 24, 2016 at 19:20

Evgeni Dimitrov · Accepted Answer · 2016-06-24 19:22:49Z

2

For older versions of SQL Server you can use insert from select

INSERT INTO PERSONALRH_NIVEL (SERHGCCAB, PUESTO_ID, COMPANIA_ID, REGION_ID, TIPO_EMPLEADO)
SELECT 81570, 4, 2001, 2, 'N' 
UNION ALL
SELECT 81570, 4, 2001, 3, 'S'
UNION ALL
SELECT 81570, 4, 2001, 3, 'N';

answered Jun 24, 2016 at 19:22

Evgeni Dimitrov

22.6k34 gold badges124 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

Great! it worked, thank you Evgeni you saved my a lot of work :D And thank you all for your time, it didnt occur to me the version was an issue

Evgeni Dimitrov Over a year ago

@David I'm glad it helped.

marc_s · Accepted Answer · 2016-06-24 20:30:26Z

0

It depends on the version of SQL Server you are using. Insert Into () VALUES (),() only works in SQL Server 2008 and newer. If you are using SQL Server 2005 you need to use separate Insert statements.

edited Jun 24, 2016 at 20:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 24, 2016 at 19:20

Kyle

1152 silver badges8 bronze badges

Collectives™ on Stack Overflow

Insert several values in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related