I am trying to copy a SQL Server database (including the IDs). I need identity insert to be on for every table. The problem I am running into is the following:
SET IDENTITY_INSERT [dbo].[abc] OFF;
SET IDENTITY_INSERT [dbo].[def] ON ; <---- Fails
I get this error:
IDENTITY_INSERT is already ON for table 'master.dbo.abc'. Cannot perform SET operation for table 'dbo.def'.
According to documentation:
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.
which is why I assume this problem is because turning the identity insert off lags behind.
I am using Sqldeveloper with jtds 1.2.8 drivers.
Is it possible to force set identity on so that it turns off in the previous table? Is there a SQL syntax which can do both of the above at once? Could the problem be caused by the driver?