SQL IDENTITY_INSERT 设置为 OFF - 如何打开它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3112579/
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
IDENTITY_INSERT is set to OFF - How to turn it ON?
提问by Spooks
I have a deleted file archive database that stores the ID of the file that was deleted, I want the admin to be able to restore the file (as well as the same ID for linking files). I do not want to take identity_insert off the entire table, as the increment by one works great. In my insert to TBL_Contentstore procedure I have something like this
我有一个已删除的文件存档数据库，用于存储已删除文件的 ID，我希望管理员能够恢复该文件（以及用于链接文件的相同 ID）。我不想从整个表中删除 identity_insert，因为增加一个效果很好。在我的插入到TBL_Content存储过程中，我有这样的东西
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET IDENTITY_INSERT tbl_content ON
GO
ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int,
...insert command...
SET IDENTITY_INSERT tbl_content OFF
But I keep getting the same error:
但我不断收到同样的错误：
Cannot insert explicit value for identity column in table 'TBL_Content' when IDENTITY_INSERT is set to OFF.
当 IDENTITY_INSERT 设置为 OFF 时，无法为表 'TBL_Content' 中的标识列插入显式值。
Any help?
有什么帮助吗？
回答by David
Should you instead be setting the identity insert to on within the stored procedure? It looks like you're setting it to on only when changing the stored procedure, not when actually calling it. Try:
您应该在存储过程中将身份插入设置为 on 吗？看起来您仅在更改存储过程时才将其设置为 on，而不是在实际调用它时。尝试：
ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int,
SET IDENTITY_INSERT tbl_content ON
...insert command...
SET IDENTITY_INSERT tbl_content OFF
GO
回答by Abe Miessler
Shouldn't you be setting identity_Insert ON, inserting the records and then turning it back off?
您不应该将 identity_Insert 设置为 ON，插入记录然后将其关闭吗？
Like this:
像这样：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET IDENTITY_INSERT tbl_content ON
GO
ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int,
SET IDENTITY_INSERT tbl_content ON
...insert command...
SET IDENTITY_INSERT tbl_content OFF
回答by DCNYAM
I believe it needs to be done in a single query batch. Basically, the GO statements are breaking your commands into multiple batches and that is causing the issue. Change it to this:
我相信它需要在单个查询批处理中完成。基本上，GO 语句将您的命令分成多个批次，这就是导致问题的原因。改成这样：
SET IDENTITY_INSERT tbl_content ON
/* GO */
...insert command...
SET IDENTITY_INSERT tbl_content OFF
GO
回答by Edu
Reminder
提醒
SQL Server only allows one table to have IDENTITY_INSERT property set to ON.
SQL Server 只允许一个表将 IDENTITY_INSERT 属性设置为 ON。
This does not work:
这不起作用：
SET IDENTITY_INSERT TableA ON
SET IDENTITY_INSERT TableB ON
... INSERT ON TableA ...
... INSERT ON TableB ...
SET IDENTITY_INSERT TableA OFF
SET IDENTITY_INSERT TableB OFF
Instead:
反而：
SET IDENTITY_INSERT TableA ON
... INSERT ON TableA ...
SET IDENTITY_INSERT TableA OFF
SET IDENTITY_INSERT TableB ON
... INSERT ON TableB ...
SET IDENTITY_INSERT TableB OFF
回答by DoesEatOats
The Reference: http://technet.microsoft.com/en-us/library/aa259221%28v=sql.80%29.aspx
参考资料：http: //technet.microsoft.com/en-us/library/aa259221%28v=sql.80%29.aspx
My table is named Genrewith the 3 columns of Id, Nameand SortOrder
我的表被命名为Genre具有的3列Id，Name并SortOrder
The code that I used is as:
我使用的代码是：
SET IDENTITY_INSERT Genre ON
INSERT INTO Genre(Id, Name, SortOrder)VALUES (12,'Moody Blues', 20)
回答by Ankit
Add this line above you Query
在您的查询上方添加此行
SET IDENTITY_INSERT tbl_content ON
回答by Narayan Yerrabachu
Add set off also
也加上出发
SET IDENTITY_INSERT Genre ON
INSERT INTO Genre(Id, Name, SortOrder)VALUES (12,'Moody Blues', 20)
SET IDENTITY_INSERT Genre OFF
