How to turn IDENTITY_INSERT on and off using SQL S

问题:

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.

回答1:

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.

回答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)

回答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

回答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.

回答5:

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

回答6:

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.

回答7:

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