0

Using simple insert statement I'm trying to insert data in a table explicitly with PK keys that have an Identity property defined on itself. But when I run the statement below I'm getting a weird error message

SET IDENTITY_INSERT MyDB.dbo.MyTable ON

Msg 8107, Level 16, State 1, Line 163
IDENTITY_INSERT is already ON for table 'MyDB.dbo.AnotherTable'. Cannot perform SET operation for table 'MyDB.dbo.MyTable '.

Why is it complaining on MyDB.dbo.AnotherTable when this Identity_Insert is specified being specified for MyDB.dbo.MyTable?

Some other tables also give similar error.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Data Engineer
  • 795
  • 16
  • 41
  • 1
    This may help you https://stackoverflow.com/questions/32861432/weird-error-msg-8107-on-sql-server-2008-r2-for-set-identity-insert – Avi Apr 14 '20 at 00:58

2 Answers2

2

According to the documentation:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

If you need to insert explicit IDENTITY values into several tables, make sure you set it to OFF for the previous table before moving to the next one.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • Any idea how to find out what table has the IDENTTY_INSERT ON for your session? – Micah Epps Jun 17 '21 at 15:56
  • 1
    @MicahEpps, I don't think so. This is specific to your connection, so it can't be extracted from system tables. Your code is supposed to "know" which table had `identity_insert` enabled for it. Reconnecting, or using a different connection might be a suitable way out of it. – Roger Wolf Jun 17 '21 at 23:28
  • I set it to ON and tried to put it in a try catch block and it just still ERRORs saying its already ON and not anymore continues which doesn't MAKE SENSE at ALL. I used it on the TEMP TABLE BTW – aj go Feb 21 '22 at 12:10
  • @ajgo, check out this answer: https://stackoverflow.com/a/14774939 - is that how you do the `insert`? – Roger Wolf Feb 22 '22 at 05:17
  • This was driving me nuts! It kept saying it was on when it was off and vice versa... Thanks for the insight. Disconnecting and reconnecting your session gets you out of it! – Kevnotec Jan 09 '23 at 11:57
0

The issue was my SQL Server 2016 connection to the server. Once I disconnected and reconnected everything works well!

Data Engineer
  • 795
  • 16
  • 41