如何使用 SQL Server 2008 打开和关闭 IDENTITY_INSERT？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7063501/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to turn IDENTITY_INSERT on and off using SQL Server 2008?
提问by Beginner
Why am I getting an error doing an insert when IDENTITY_INSERTis set to OFF?
为什么在IDENTITY_INSERT设置为 OFF时执行插入时出错？
How do I turn it on properly in SQL Server 2008? Is it by using SQL Server Management Studio?
如何在 SQL Server 2008 中正确打开它？是通过使用 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.
回答by gbn
Via SQL as per MSDN
根据MSDN通过 SQL
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 exactlywhat is wrong...
完整的错误消息会告诉您究竟出了什么问题...
Cannot insert explicit value for identity column in table 'sometableWithIdentity' when IDENTITY_INSERT is set to OFF.
当 IDENTITY_INSERT 设置为 OFF 时，无法为表 'sometableWithIdentity' 中的标识列插入显式值。
回答by Ismael
I had a problem where it did not allow me to insert it even after setting the IDENTITY_INSERT ON.
我遇到了一个问题，即使将 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)
所以基本上做完整的 INSERT INTO tbl(cols) Values(vals)
回答by R.Alonso
Import:You must write columns in INSERTstatement
导入：您必须在INSERT语句中写入列
INSERT INTO TABLE
SELECT * FROM
Is not correct.
是不正确的。
Insert into Table(Field1,...)
Select (Field1,...) from TABLE
Is correct
是正确的
回答by aok
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.
我知道这是一个较旧的线程，但我只是碰到了这个。如果用户在其他一些会话 Set IDENTITY_INSERT ON 之后尝试在 Identity 列上运行插入，那么他一定会得到上述错误。
Setting the Identity Insert value and the subsequent Insert DML commands are to be run by the same session.
设置 Identity Insert 值和后续的 Insert DML 命令将由同一会话运行。
Here @Beginner was setting Identity Insert ON separately and then running the inserts from his application. That is why he got the below Error:
这里@Beginner 单独设置 Identity Insert ON，然后从他的应用程序运行插入。这就是为什么他得到以下错误：
Cannot insert explicit value for identity column in table 'Baskets' when
IDENTITY_INSERT is set to OFF.
回答by Jettero
It looks necessary to put a SET IDENTITY_INSERT Database.dbo.Baskets ON;before every SQL INSERTsending batch.
看起来有必要SET IDENTITY_INSERT Database.dbo.Baskets ON;在每个 SQLINSERT发送批处理之前放一个。
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.
您可以发送多个INSERT ... VALUES ...以一个SET IDENTITY_INSERT ... ON;字符串开头的命令。只是不要在它们之间放置任何批处理分隔符。
I don't know why the SET IDENTITY_INSERT ... ONstops 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.
我不知道为什么在SET IDENTITY_INSERT ... ON发送块之后停止工作（例如：.ExecuteNonQuery()在 C# 中）。我不得不SET IDENTITY_INSERT ... ON;再次放在下一个 SQL 命令字符串的开头。
回答by seanyp20001
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
当您有一个 PRIMARY KEY 字段并且您正在插入一个重复的值或者您将 INSERT_IDENTITY 标志设置为 on 时，这很可能
回答by Bruce
You need to add the command 'go' after you set the identity insert. Example:
设置身份插入后，您需要添加命令“go”。例子：
SET IDENTITY_INSERT sometableWithIdentity ON
go
INSERT sometableWithIdentity (IdentityColumn, col2, col3, ...)
VALUES (AnIdentityValue, col2value, col3value, ...)
SET IDENTITY_INSERT sometableWithIdentity OFF
go
