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

Question

I have two tables tblData1and tblData2, and now I want to transfer records from another table with an insert for identification, and I'm 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 shows an error as shown below

IDENTITY_INSERT is already enabled for the table 'Sample_Training.dbo.tblData1'. Cannot perform SET operation on table 'dbo.tblData2'

Is it possible to run multiple IDENTITY_INSERTin SQL Server 2008

Answer 1

IDENTITY_INSERT, ON.

, , , .
, .
, - .

Answer 2

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 

Answer 3

You can set Identity_Insert for only one table at a time in one session. If there are no data dependencies between tables, you can open several sessions, each of which processes a different set of tables. Each session can set one table for identy_insert.