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

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.

Answers


Via SQL as per MSDN

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT 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.


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)


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


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.

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


It looks nevessary 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.


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

Need Your Help

Row Offset in SQL Server

sql sql-server

Is there any way in SQL Server to get the results starting at a given offset? For example, in another type of SQL database, it's possible to do:

Are there any clones/alternatives for running a Stack Exchange style Q&A site?

discussion stack-exchange clone

Since the source code for this site isn't available for purchase or external use, I'm looking for software that can run a Q&A environment similar to Stack Exchange. Are there any such pieces of