SQL syntax error 170 in insert statement

问题:

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' ) 

回答1:

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).

回答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.

回答3:

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) 

回答4:

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/