SQL syntax error 170 in insert statement

Question

here is my SQL statement and I am getting Error 170 Incorrect syntax near ',' at line 4

INSERT INTO SEO_Permalink_Test 
( IDObjekt ,IDType ,IDLanguage ,StateSEOName ,StateSEOPermalink ,DatumErstellungSEOName ,DatumLetzteAenderungSEOName ,SEOName)
 VALUES 
 ( 19988 , 72 , 1 , 0 , 0 , GETDATE() , GETDATE() , 'H_KDA1559_ST_004' ), 
 ( 19989 , 72 , 1 , 0 , 0 , GETDATE() , GETDATE() , 'H_KDA1559_FS_003' ) , 
 ( 19997 , 72 , 1 , 0 , 0 , GETDATE() , GETDATE() , 'H_CAU0171_WO_015' ) , 
 ( 19998 , 72 , 1 , 0 , 0 ,  GETDATE() , GETDATE() , 'H_KDA1559_ST_003' ) , 
 ( 19999 , 72 , 1 , 0 , 0 ,  GETDATE() , GETDATE() , 'H_KDA1559_FS_001' ) , 
 ( 20001 , 72 , 1 , 0 , 0 ,  GETDATE() , GETDATE() , 'H_ILI0758_AU_007' ) , 
 ( 20002 , 72 , 1 , 0 , 0 ,  GETDATE() , GETDATE() , 'H_ILI0758_PO_011' ) , 
 ( 20003 , 72 , 1 , 0 , 0 ,  GETDATE() , GETDATE() , 'H_ILI0758_RS_008' ) , 
 ( 20004 , 72 , 1 , 0 , 0 ,  GETDATE() , GETDATE() , 'H_CIT0550_WO_002' ) , 
 ( 20005 , 72 , 1 , 0 , 0 ,  GETDATE() , GETDATE() , 'H_CIT0550_WO_003' ) , 
 ( 20006 , 72 , 1 , 0 , 0 ,  GETDATE() , GETDATE() , 'H_CIT0550_TR_001' ) , 
 ( 20007 , 72 , 1 , 0 , 0 ,  GETDATE() , GETDATE() , 'H_CIT0550_RS_001' ) , 
 ( 20008 , 72 , 1 , 0 , 0 ,  GETDATE() , GETDATE() , 'H_CSL0152_AU_001' )

which version sql server that you use? I copy your code and it's work in SQL Server 2008 — Iswanto San
– Iswanto San, Commented Jul 5, 2013 at 14:30
What version of SQL Server are you using? Multiple VALUES was only added in 2008. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 5, 2013 at 14:31
Why have you reposted your question? getting syntax error in insert statement — Danny Beckett
– Danny Beckett, Commented Jul 5, 2013 at 14:34

LittleSweetSeas · Accepted Answer · 2013-07-05 14:46:14Z

2

You cannot perform a multi-row insert into SQL Server prior than 2008. You have two ways to do it:

INSERT INTO SEO_Permalink_Test 
( IDObjekt ,IDType ,IDLanguage ,StateSEOName ,StateSEOPermalink ,DatumErstellungSEOName ,DatumLetzteAenderungSEOName ,SEOName)
 SELECT 19988 , 72 , 1 , 0 , 0 , GETDATE() , GETDATE() , 'H_KDA1559_ST_004' UNION ALL 
 SELECT 19989 , 72 , 1 , 0 , 0 , GETDATE() , GETDATE() , 'H_KDA1559_FS_003' 
...

Or

INSERT INTO SEO_Permalink_Test 
( IDObjekt ,IDType ,IDLanguage ,StateSEOName ,StateSEOPermalink ,DatumErstellungSEOName ,DatumLetzteAenderungSEOName ,SEOName)
 VALUES 
 ( 19988 , 72 , 1 , 0 , 0 , GETDATE() , GETDATE() , 'H_KDA1559_ST_004' )

INSERT INTO SEO_Permalink_Test 
( IDObjekt ,IDType ,IDLanguage ,StateSEOName ,StateSEOPermalink ,DatumErstellungSEOName ,DatumLetzteAenderungSEOName ,SEOName)
 VALUES 
 ( 19989 ,

I would go with the 1st way as it's less verbose.

edited Jul 5, 2013 at 14:46

answered Jul 5, 2013 at 14:30

LittleSweetSeas

7,1242 gold badges24 silver badges27 bronze badges

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

3 Comments

RBarryYoung Over a year ago

Actually this is called "Row Construction", as BULK INSERT is an explicit command in T-SQL for importing a file, and thus a completely different thing.

LittleSweetSeas Over a year ago

I accept your comment but note that i used the "bulk insert" term not in a specific way or as a command, rather than as a descriptive term to perform a multi-row (bulk, in english) insert.

Damien_The_Unbeliever Over a year ago

I agree with @RBarryYoung - using a term like Bulk Insert, when that is a specific term for a different feature of the same product is at best misleading.

shahkalpesh · Accepted Answer · 2013-07-05 14:34:54Z

0

I am guessing that it is SQL Server 2005 or below

From: http://msdn.microsoft.com/en-us/library/ms174335%28v=sql.100%29.aspx

SQL Server 2008 introduces the Transact-SQL row constructor (also called a table value constructor) to specify multiple rows in a single INSERT statement. The row constructor consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma. For more information, see Table Value Constructor (Transact-SQL).

answered Jul 5, 2013 at 14:34

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Comments

Curt · Accepted Answer · 2013-07-05 14:32:07Z

0

Just a syntax problem: you need to have a separate insert statement for each record.

Another alternative, which may add to readability, is to structure the statement like the following

 INSERT INTO TableName(col1, col2, col3)
         SELECT( a, b, c) 
   UNION SELECT( d, e, f) 
   UNION SELECT( g, h, i)

answered Jul 5, 2013 at 14:32

Curt

5,7511 gold badge25 silver badges36 bronze badges

1 Comment

LittleSweetSeas Over a year ago

Please add the "ALL" keyword to "UNION" to avoid unnecessary overhead

donstack · Accepted Answer · 2013-07-05 14:38:47Z

0

your insert statement is parsed in my sql error free.

INSERT INTO MyTable (FirstCol, SecondCol)
    VALUES ('First',1),
    ('Second',2),
    ('Third',3),
    ('Fourth',4),
    ('Fifth',5)

http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/

answered Jul 5, 2013 at 14:38

donstack

2,7253 gold badges32 silver badges47 bronze badges

Collectives™ on Stack Overflow

SQL syntax error 170 in insert statement

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related