2

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;
Improve this question
8
  • Dropping and recreating the table does not resolve the issue. – MPelletier Mar 20 '12 at 14:46
  • Are you doing this through 2 query windows? Only one connection can have that set for a single table I believe. – Thomas Stringer Mar 20 '12 at 14:50
  • @Shark - They both can have it set but obviously setting in one only then doesn't automatically allow the other to insert explicit values. – Martin Smith Mar 20 '12 at 14:52
  • Same window. My script is exactly that (save for the actual table structure. The Set followed by the Insert. Followed by a final Set to turn it back off. – MPelletier Mar 20 '12 at 14:52
  • @MPelletier - So you aren't using any dynamic SQL or stored procedures or anything? If turned on in a child batch it gets reset automatically when that exits. – Martin Smith Mar 20 '12 at 14:56
4

Is it possible you have two tables with the same name in different schemas? Some random doc I found online states that message 8107 appears when you already have IDENTITY_INSERT set to ON for another table in the db...

Another debugging step to check: You state that the issue keep occurring even after you drop and recreate the table, but what if you drop and don't recreate the table, and try to enable IDENTITY_INSERT on another table? What happens then?

Improve this answer
3
  • Same table names. Dammit, I can't post my actual code (as per contract) and that makes you guys look at the wrong things. Alright, I'll work with that. – MPelletier Mar 20 '12 at 15:25
  • 1
    @MPelletier always specify schema name. sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/… – Aaron Bertrand Mar 20 '12 at 15:45
  • @AaronBertrand That I always did, on my side. I don't know if it's the connection, but ultimately I started a new one, encapsulated the operations in a transaction, and now it works. – MPelletier Mar 20 '12 at 16:08
0

I was struggling with the same thing. I noticed that te error message also gave a database name in the 'full' table name, like: database.schema.table. I entered command to set IDENTITY_INSERT OFF for this full name and everything started working after that.

Improve this answer
1
  • 1
    Before adding the database name to the reference, were you referencing the target table exactly the same way in both the SET IDENTITY_INSERT and the INSERT command? And are you sure both commands were executing in the context of the same database? Because if the answer is "yes" to both, you couldn't possibly get the Msg 544 error. You could have other issues, but not that one. – Andriy M Feb 20 '18 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.