SQL syntax error with dynamically constructed INSERT?

Question

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

Your example and executing code are two different things. You need to use the column list instead of Select *. Simply make your execution query like your example without identity column. — Indi
– Indi, Commented Nov 1, 2013 at 10:23

Szymon · Accepted Answer · 2013-11-01 10:36:09Z

2

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)

answered Nov 1, 2013 at 10:36

Szymon

43k16 gold badges99 silver badges115 bronze badges

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

2 Comments

user2520671 Over a year ago

What would i do if i wanted to select all tables from @source_ Database_Name, tProperty is just for testing

Szymon Over a year ago

You mean including identity column?

Collectives™ on Stack Overflow

SQL syntax error with dynamically constructed INSERT?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related