How to turn IDENTITY_INSERT on and off using SQL Server 2008?

531
votes

Why am I getting an error doing an insert when IDENTITY_INSERT is set to OFF?

How do I turn it on properly in SQL Server 2008? Is it by using SQL Server Management Studio?

I have run this query:

SET IDENTITY_INSERT Database. dbo. Baskets ON

Then I got the message back in the console that the Command(s) completed successfully. However when I run the application, it still gives me the error shown below:

Cannot insert explicit value for identity column in table 'Baskets' when 
IDENTITY_INSERT is set to OFF.
8
sqlsql-server-2008
Please expand this question so we can understand what you're trying to do. What statement are you running when you encounter this error, and what is the actual text of the error message? – Matt Gibson
Even though you asked another question, you should accept an answer here: we answered what you asked. – gbn
I have a horror of the idea that someone is trying to send identity values from an application. This is not something that you should be doing except for the infrequent data migration. If you are doing this regularly enough to run it from an application, then you probably need to revisit your table design. – HLGEM
I'm using this in my SQL scripted backup. During restore this let me to load the data into an Autoincremented table with original values. The scripted backup let me to downgrade the SQL database. The Management Studio's realisation of Scripted backup makes unloadable script if I store binary data in a text field. – Jettero

8 Answers

874
votes

Via SQL as per MSDN

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT INTO sometableWithIdentity 
    (IdentityColumn, col2, col3, ...)
VALUES 
    (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF

The complete error message tells you exactly what is wrong...

Cannot insert explicit value for identity column in table 'sometableWithIdentity' when IDENTITY_INSERT is set to OFF.

66
votes

I had a problem where it did not allow me to insert it even after setting the IDENTITY_INSERT ON.

The problem was that i did not specify the column names and for some reason it did not like it. 

INSERT INTO tbl Values(vals)

So basically do the full INSERT INTO tbl(cols) Values(vals)

40
votes

Import: You must write columns in INSERT statement

INSERT INTO TABLE
SELECT * FROM

Is not correct.

Insert into Table(Field1,...)
Select (Field1,...) from TABLE

Is correct

8
votes

I know this is an older thread but I just bumped into this. If the user is trying to run inserts on the Identity column after some other session Set IDENTITY_INSERT ON, then he is bound to get the above error.

Setting the Identity Insert value and the subsequent Insert DML commands are to be run by the same session.

Here @Beginner was setting Identity Insert ON separately and then running the inserts from his application. That is why he got the below Error:

Cannot insert explicit value for identity column in table 'Baskets' when 
IDENTITY_INSERT is set to OFF.
1
votes

It looks necessary to put a SET IDENTITY_INSERT Database.dbo.Baskets ON; before every SQL INSERT sending batch.

You can send several INSERT ... VALUES ... commands started with one SET IDENTITY_INSERT ... ON; string at the beginning. Just don't put any batch separator between.

I don't know why the SET IDENTITY_INSERT ... ON stops working after the sending block (for ex.: .ExecuteNonQuery() in C#). I had to put SET IDENTITY_INSERT ... ON; again at the beginning of next SQL command string.

0
votes

This is likely when you have a PRIMARY KEY field and you are inserting a value that is duplicating or you have the INSERT_IDENTITY flag set to on

0
votes

Another option is where you have tables like 'type' or 'status', for example, OrderStatus, where you always want to control the Id value, create the Id (Primary Key) column without it being an Identity column is the first place.

-1
votes

You need to add the command 'go' after you set the identity insert. Example:

SET IDENTITY_INSERT sometableWithIdentity ON
go

INSERT sometableWithIdentity (IdentityColumn, col2, col3, ...)
VALUES (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF
go