How to SET IDENTITY_INSERT ON in SQL Server 2008 for multiple tables at once
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.
So before enabling the other one, you should turn of existing if any.
If it is lesser number of tables you can turn on and turn off before and after your operations.
If the table count is huge, you should automate somehow to enable and disable before your operations.
Did you try changing the order
goSET IDENTITY_INSERT LP1.dbo.tblData1 ONINSERT INTO LP1.DBO.tblData1 (ID,DATA)SELECT ID,DATAFROM LP.DBO.tblData1SET IDENTITY_INSERT LP1.dbo.tblData1 OFFGOSET IDENTITY_INSERT LP1.dbo.tblData2 ONINSERT INTO LP1.DBO.tblData2 (ID,DATA)SELECT ID,DATAFROM LP.DBO.tblData2SET IDENTITY_INSERT LP1.dbo.tblData2 OFFGO
You can only set Identity_Insert for one table at a time in a single session.If there are no data dependancies between the tables, then you can open several sessions, each handling a different set of tables. Each session can set one table for identy_insert.