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

Question

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.

Unbrace

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? – Pearcy

Even though you asked another question, you should accept an answer here: we answered what you asked. – Avraham

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. – Splashy

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. – Destructionist

Answer 1

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.

Avraham

@Beginner: you are though, hence the error. How are we to know that you don't want to insert values? We only have an error message – Avraham

Error is from my app when i do DB.SaveChanges(); – Unbrace

Do i have to run this within the application also every time? – Unbrace

No, just stop sending a value for the identity column. Or set it if you want in your app if you want to send a value..... but then why have a column with the identity property set to generate values? We can't decide for you – Avraham

@Beginner: The setting is only applicable in the current session (no one quite pointed that out for this question), so your application would have to turn it on in order for the application to perform such inserts (and it's probably best to promptly turn it off again when such inserts are concluded, like gbn shows). How you were inserting a value on the identity column without realizing it isn't clear, but perhaps older versions of SQL Server would include it implicitly with all columns when explicit insert columns are not specified (?) as Ismael seems to suggest. – Tomtoma

@Rob, I think you havea point adn I personally fail code review on any code that does na insert without specifying columns. Aside from the time wasted on an error, this is a risky practice and you can end up with serious data integrity problems if the column data doesn't match up correctly. – Splashy

Just curious, is there a way to do this for Update as well as Insert? – Neuro

Is it neccessary to set Identity_Insert to OFF after the batch is complete OR is that just a coding practice? – Licensee

Writing the column names here is important, If you do not write the column names or use a select * from than you will get the error An explicit value for the identity column in table 'tableName' can only be specified when a column list is used and IDENTITY_INSERT is ON. – Shelton

Answer 2

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)

Catiline

this was exactly what it wanted of me, to specify the column list (both columns on the table, blah) – Kenlee

I had something similar, it appeared that the library I was calling should have called 'Update' instead of 'Save'. Took me a few hours to find out I did not had to change the Table properties, but the calling code. – Nereid

Answer 3

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

Sandblind

If you have identical columns in both tables you can do without listing the columns in the select(Field1..) like this....Insert into Table(Field1,...) Select * from TABLE... and if you have Identity field to insert first do**SET IDENTITY_INSERT Table_name ON – Cherycherye

@Cherycherye That is a risky assumption to make, that the column list is identical. Outside of manual user queries, never do that, because I guarantee you will break PROD one day when somebody else modifies the table definition without updating your reference. – Phenomenal

Answer 4

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.

Pox

Even doing the SET IDENTITY_INSERT in one Management Studio tab and the INSERT in a different one, caused the error for me. Had to do both in same window tab. – Hairless

Answer 5

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.

Destructionist

maybe this answers your question: #5792441 – Fiacre

Answer 6

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

Stipulate

Answer 7

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.

Postlude