blocks|key|3036124|text|我认为这需要在单个查询批处理中完成。基本上，GO语句将您的命令分成多个批处理，这就是导致问题的原因。将其更改为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3036125|SET+IDENTITY_INSERT+tbl_content+ON
/*+GO+*/

...insert+command...

SET+IDENTITY_INSERT+tbl_content+OFF
GO|code-block|syntax|javascript|3036126|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>SET IDENTITY_INSERT tbl_content ON
/* GO */

...insert command...

SET IDENTITY_INSERT tbl_content OFF
GO
</code></pre>

blocks|key|304726|text|您不应该将identity_Insert设置为打开，插入记录，然后再将其关闭吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|304727|如下所示：|304728|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|code-block|syntax|javascript|304729|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Shouldn't you be setting identity_Insert ON, inserting the records and then turning it back off?

Like this:

<pre><code>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
</code></pre>

blocks|key|304731|text|您是否应该在存储过程中将identity+insert设置为on？看起来您只在更改存储过程时才将其设置为on，而不是在实际调用它时。尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|304732|ALTER+procedure+[dbo].[spInsertDeletedIntoTBLContent]
@ContentID+int,+

SET+IDENTITY_INSERT+tbl_content+ON

...insert+command...

SET+IDENTITY_INSERT+tbl_content+OFF
GO|code-block|syntax|javascript|304733|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int, 

SET IDENTITY_INSERT tbl_content ON

...insert command...

SET IDENTITY_INSERT tbl_content OFF
GO
</code></pre>

blocks|key|3250329|text|参考资料：http://technet.microsoft.com/en-us/library/aa259221%2528v=sql.80%2529.aspx|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3250330|我的表名为Genre，包含Id、Name和SortOrder这3列|style|CODE|3250331|我使用的代码是：|3250332|SET+IDENTITY_INSERT+Genre+ON

INSERT+INTO+Genre(Id,+Name,+SortOrder)VALUES+(12,'Moody+Blues',+20)+|code-block|syntax|javascript|3250333|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/aa259221%2528v=sql.80%2529.aspx^0|5|1Y|0|0|5|5|D|2|G|4|L|9|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@$A|11|B|12|F|G]|$A|13|B|14|F|G]|$A|15|B|16|F|G]|$A|17|B|18|F|G]]|9|@]|C|$]]|$1|H|3|I|5|6|7|19|8|@]|9|@]|C|$]]|$1|J|3|K|5|L|7|1A|8|@]|9|@]|C|$M|N]]|$1|O|3|-4|5|6|7|1B|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

The Reference: <a href="http://technet.microsoft.com/en-us/library/aa259221%28v=sql.80%29.aspx" rel="nofollow noreferrer">http://technet.microsoft.com/en-us/library/aa259221%28v=sql.80%29.aspx</a>

My table is named <code>Genre</code> with the 3 columns of <code>Id</code>, <code>Name</code> and <code>SortOrder</code>

The code that I used is as:

<pre><code>SET IDENTITY_INSERT Genre ON

INSERT INTO Genre(Id, Name, SortOrder)VALUES (12,'Moody Blues', 20) 
</code></pre>

blocks|key|303934|text|将此行添加到查询的上方|type|unstyled|depth|inlineStyleRanges|entityRanges|data|303935|SET+IDENTITY_INSERT+tbl_content+ON|code-block|syntax|javascript|303936|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Add this line above you Query

<pre><code>SET IDENTITY_INSERT tbl_content ON
</code></pre>

blocks|key|3250360|text|提醒|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3250361|SQL+Server只允许一个表的IDENTITY_INSERT属性设置为ON。|3250362|这不起作用：|3250363|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|code-block|syntax|javascript|3250364|而是：|3250365|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|3250366|entityMap^0|0|2|0|0|14|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|10|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|11|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|12|8|@]|D|@]|E|$]]|$1|Q|3|R|5|L|7|13|8|@]|D|@]|E|$M|N]]|$1|S|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|T|$]]

Reminder

SQL Server only allows one table to have IDENTITY_INSERT property set to ON.

This does not work:

<pre><code>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
</code></pre>

Instead:

<pre><code>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
</code></pre>

blocks|key|3250370|text|同时添加set+off|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3250371|+SET+IDENTITY_INSERT+Genre+ON

++++INSERT+INTO+Genre(Id,+Name,+SortOrder)VALUES+(12,'Moody+Blues',+20)+

++++SET+IDENTITY_INSERT+Genre++OFF|code-block|syntax|javascript|3250372|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Add set off also

<pre><code> SET IDENTITY_INSERT Genre ON

 INSERT INTO Genre(Id, Name, SortOrder)VALUES (12,'Moody Blues', 20) 

 SET IDENTITY_INSERT Genre OFF
</code></pre>

blocks|key|3250384|text|我有两个不同的表，对于第一个表，插入可以正常工作，但是对于第二个表，抛出了关于标识插入的异常。问题是表有一个字段(Int)作为伪造关键字，它没有填充。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3250385|entityMap^0|1E|O|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

I had two different tables and for the first one insert works fine but for the second one throws this exception regarding Identity Insert.
The issue was that the table had a field(int) as a forgery key which wasn't populated.

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 <code>TBL_Content</code> store procedure I have something like this

<pre><code>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
</code></pre>

But I keep getting the same error:

<blockquote>
 Cannot insert explicit value for identity column in table
 'TBL_Content' when IDENTITY_INSERT is set to OFF.
</blockquote>

Any help?

IDENTITY_INSERT is set to OFF - How to turn it ON?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我有一个已删除的文件存档数据库，其中存储了被删除的文件的ID，我希望管理员能够恢复该文件(以及链接文件的相同ID )。我不想将identity_insert从整个表中删除，因为递增1的效果很好。在我的insert to TBL_Content存储过程中，我有如下内容set ANSI_NULLS ONset QUOTED...

问IDENTITY_INSERT设置为OFF -如何将其打开？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问IDENTITY_INSERT设置为OFF -如何将其打开？EN