How to SET IDENTITY_INSERT ON in SQL Server 2008 for multiple tables at once

Question

I have two tables tblData1 and tblData2 and now I want to migrate records from another table with identity insert and I am trying to run a command as shown below

SET IDENTITY_INSERT LP1.dbo.tblData1 ON
GO

SET IDENTITY_INSERT LP1.dbo.tblData2 ON
GO

INSERT INTO LP1.DBO.tblData1 (ID,DATA)
SELECT ID,DATA FROM LP.DBO.tblData1
GO

INSERT INTO LP1.DBO.tblData2 (ID,DATA)
SELECT ID,DATA FROM LP.DBO.tblData2
GO

SET IDENTITY_INSERT LP1.dbo.tblData1 OFF
GO

SET IDENTITY_INSERT LP1.dbo.tblData2 OFF
GO

But it is showing error as below

IDENTITY_INSERT is already ON for table 'Sample_Training.dbo.tblData1'. Cannot perform SET operation for table 'dbo.tblData2'

Is it possible to perform multiple IDENTITY_INSERT at time in SQL Server 2008

Solution 1

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.

Solution 2

Did you try changing the order

go

SET IDENTITY_INSERT LP1.dbo.tblData1 ON

INSERT INTO LP1.DBO.tblData1
            (ID,DATA)
SELECT ID,DATA
FROM   LP.DBO.tblData1

SET IDENTITY_INSERT LP1.dbo.tblData1 OFF

GO

SET IDENTITY_INSERT LP1.dbo.tblData2 ON

INSERT INTO LP1.DBO.tblData2
            (ID,DATA)
SELECT ID,DATA
FROM   LP.DBO.tblData2

SET IDENTITY_INSERT LP1.dbo.tblData2 OFF

GO 

Solution 3

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.

Comments

[Dinesh Reddy Alla]

Yes i tried but i am having plenty of tables so i am searching weather it is possible as i mentioned above.

[Pரதீப்]

@Dineshalla try to generate the insert scripts dynamically in above order

[Dinesh Reddy Alla]

I need to migrate tables data to AMAZON server so i am trying like this.

[Dinesh Reddy Alla]

I need to migrate tables data to AMAZON server so i am trying like this. Is there any better option

[knkarthick24]

No you should generate your insert statements to your target db dynamically prefixed and affixed with SET IDENTITY_INSERT.

[Dinesh Reddy Alla]

Is it possible to perform multiple IDENTITY_INSERT at time in SQL Server 2008

[Amir Pelled]

From different sessions it should be. Its very easy to set up a test case to check. Create two tables with identity columns. Then from two different sessions set one of the tables to Identity_Insert On and insert a record. I did this on 2012 to verify. I don't have a 2008 environment to check.