I might be forgetting something, but essentially, I'm struggling with IDENTITY_INSERT.
I'm trying to move data between tables, both of which have an identity column (similar schema).
SET IDENTITY_INSERT DestinationTable ON;
returns that IDENTITY_INSERT is already ON for that table. (Error Msg 8107) Fine, it's already ON, I can work with that, right?
Then:
INSERT INTO DestinationTable (Id, ColA) SELECT Id, ColA FROM SourceTable;
returns that I can't insert an explicit value .. when IDENTITY_INSERT is OFF (Error Msg 544). But it just said it was ON.
So, what's going on?
EDIT: Actual script (save for table names and structure):
SET IDENTITY_INSERT User2.DestinationTable ON;
INSERT INTO User2.DestinationTable (Id, ColA) SELECT Id, ColA FROM User1.SourceTable;
SET IDENTITY_INSERT User2.DestinationTable OFF;