3

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;
8
  • 1
    Dropping and recreating the table does not resolve the issue.
    – MPelletier
    Mar 20, 2012 at 14:46
  • Are you doing this through 2 query windows? Only one connection can have that set for a single table I believe. Mar 20, 2012 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. Mar 20, 2012 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, 2012 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. Mar 20, 2012 at 14:56

3 Answers 3

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?

1
  • 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, 2012 at 15:25
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.

2
  • 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, 2018 at 15:27
  • @AndriyM Not saying you're wrong; you're probably not. However, The original problem isn't supposed to be possible either. When setting identity insert on gives you an error message that it's already on, but trying to insert a record with an identity gives you an error that it must be turned on... that's not "possible" either. Or turning it off successfully, and then immediately turning it back on and getting the same error message that it's already on... that's not possible either. Yet that's what I experienced which I consider to be a bug, and in which case this may very well have fixed it.
    – BVernon
    Sep 28, 2022 at 15:57
-1

Just had this same problem. I think it may be an issue I had where I lost connection to the server (VPN disconnected) and when I reconnected I copy/pasted my sql into a new window because pressing CTRL-N with the proper database highlighted in Object Explorer was faster than reconnecting the existing window.

I also tried dropping/recreating the table to no avail. But what worked for me was creating yet another window, copy/pasting my code to it, and then closing the previous window. For some reason that finally allowed me to set identity insert on without an error wheras before I was encountering the same issue.

1
  • Strange... I shared the exact same problem and the thing that worked for me but it was down voted. I don't mind that it was downvoted, but but would love to learn why... not even because I care about the downvote but this suggests that someone knows there is something wrong with my answer... perhaps I am somehow misunderstanding the events that I am sharing? What am I missing here? It was kind of an annoying error and I would love to know more about the real cause if someone thinks my experience isn't really valid.
    – BVernon
    Sep 28, 2022 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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