当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - SQL syntax error with dynamically constructed INSERT?

问题描述:

I get the below error

An explicit value for the identity column in table 'c365online_script1.dbo.tProperty' can only be specified when a column list is used and IDENTITY_INSERT is ON.

The problem is with the statement that is dynamically constructed inside the two nested cursors from what I gather it should look something like

INSERT INTO dbo.Table(col1, col2, ...., colN) VALUES(Val1, val2, ...., ValN)

I am however unsure how I would construct the BELOW INSERT statement to resemble the above?.

EXEC('INSERT INTO ' + @Destination_Database_Name + '.dbo.' + @tablename + ' SELECT * FROM ' + @Source_Database_Name + '.dbo.' + @tablename + ' WHERE ' + @Source_Database_Name + '.dbo.' + @tablename + '.CompanyID = ' + @Company_Id)

SET @Counter = 1 -- set the counter to make sure we execute loop only once.

END

网友答案:

You need to specify the list of columns because you don't insert into all of them (you don't insert into identity column). I'm guessing you're inserting from a table with the same structure from a different database - you need to specify all the source columns too in this case.

Your query will be (edit the column names):

EXEC('INSERT INTO ' + @Destination_Database_Name + '.dbo.' + @tablename + '(col1, col2, col3) SELECT col1, col2, col3 FROM ' + @Source_Database_Name + '.dbo.' + @tablename + ' WHERE ' + @Source_Database_Name + '.dbo.' + @tablename + '.CompanyID = ' + @Company_Id)
分享给朋友:
您可能感兴趣的文章:
随机阅读: