如何使用SQL Server 2008打开和关闭IDENTITY_INSERT？

本文翻译自：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? 当IDENTITY_INSERT设置为OFF时，为什么我在插入时出错？

How do I turn it on properly in SQL Server 2008? 如何在SQL Server 2008中正确启用它？ Is it by using SQL Server Management Studio? 是使用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楼

参考：https://stackoom.com/question/TdXR/如何使用SQL-Server-打开和关闭IDENTITY-INSERT

---

#2楼

I had a problem where it did not allow me to insert it even after setting the IDENTITY_INSERT ON. 我有一个问题，即使在设置IDENTITY_INSERT后，它也不允许我插入它。

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) 所以基本上完​​成INSERT INTO tbl（cols）值（vals）

---

#3楼

当您有一个PRIMARY KEY字段并且您正在插入一个重复的值或者您将INSERT_IDENTITY标志设置为on时，这很可能

---

#4楼

You need to add the command 'go' after you set the identity insert. 设置标识插入后，需要添加命令“go”。 Example: 例：

SET IDENTITY_INSERT sometableWithIdentity ON
go

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

SET IDENTITY_INSERT sometableWithIdentity OFF
go

---

#5楼

Import: You must write columns in INSERT statement 导入：您必须在INSERT语句中写入列

INSERT INTO TABLE
SELECT * FROM    

Is not correct. 是不正确的。

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

Is correct 是正确的

---

#6楼

It looks necessary to put a SET IDENTITY_INSERT Database.dbo.Baskets ON; 看起来有必要将SET IDENTITY_INSERT Database.dbo.Baskets ON;置于SET IDENTITY_INSERT Database.dbo.Baskets ON; before every SQL INSERT sending batch. 在每个SQL INSERT发送批处理之前。

You can send several INSERT ... VALUES ... commands started with one SET IDENTITY_INSERT ... ON; 您可以使用一个SET IDENTITY_INSERT ... ON;发送几个INSERT ... VALUES ...命令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#). 我不知道为什么SET IDENTITY_INSERT ... ON在发送块之后停止工作（例如C＃中的.ExecuteNonQuery() ）。 I had to put SET IDENTITY_INSERT ... ON; 我不得不把SET IDENTITY_INSERT ... ON; again at the beginning of next SQL command string. 再次在下一个SQL命令字符串的开头。