SQL Server错误:“无法为标识列插入显式值"即使我将IDENTITY

本文介绍了SQL Server错误:“无法为标识列插入显式值"即使我将IDENTITY_INSERT设置为ON的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我真的复习了几次，这就是我要问的原因；寻找指导...

I REALLY review several times, that's the reason I am asking; looking for guidance...

我有一张桌子，如下脚本.然后，设置IDENTITY_INSERT ON.然后，我尝试进行插入选择，(我需要非常相同的ID)

I have one table, as the script below. Then, I set IDENTITY_INSERT ON. Then I try to do an insert select, (I NEED the very same ids)

我不断收到此错误:

信息544，第16级，状态1，第2行 当IDENTITY_INSERT设置为OFF时，无法为表'Table1'中的标识列插入显式值.

Msg 544, Level 16, State 1, Line 2 Cannot insert explicit value for identity column in table 'Table1' when IDENTITY_INSERT is set to OFF.

有人知道为什么吗?在数据库级别进行的任何设置都可以否决IDENTITY_INSERT ON吗?

Does anybody knows why? Any set up at DB level can overrule the IDENTITY_INSERT ON?

我感谢任何建议.在此先感谢您，并致以亲切的问候.

I appreciate any advice. Thanks in advance and kind regards.

表格脚本:

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].Table1 ( [TableId] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](256) NOT NULL, [RowVersion] [timestamp] NOT NULL, [AddedDate] [datetime2](7) NOT NULL, [stuff2] [int] NOT NULL, [ModifiedDate] [datetime2](7) NOT NULL, [LastModifiedBy] [int] NOT NULL, CONSTRAINT [Table1_PK] PRIMARY KEY CLUSTERED ([TableId] ASC) ) ON [PRIMARY] GO

insert语句:

SET IDENTITY_INSERT [dbo].Table1 ON; INSERT INTO [dbo].Table1 ([TableId], [Name], [AddedDate], [stuff2], [ModifiedDate], [LastModifiedBy]) SELECT [RoleID], [Name], [AddedDate], [stuff2], [ModifiedDate], [LastModifiedBy] FROM [dbo].Table2

推荐答案

非常感谢@Sami，您可以帮助我实现正确的道路. 事实证明，您可以只将IDENTITY_INSERT一次用于一个表(出于明显的原因，我经常不这样做). 当我一次处理多个表时，我看到了错误，但是由于表的名称相似，我认为它引发了错误，因为我之前在同一张表上运行过Identity_insert，但这是因为它是由另一张桌子. 直到我逐一查看错误消息后，我才意识到. :P:D

Thank you so much @Sami, you help me to realize the right path. It turns out, you can just use, IDENTITY_INSERT to one table at time (for obvious reasons is not a thing I do often). When I did for several tables at time, I saw the error, but as the name of the tables were similar, I thought it was throwing an error because I ran the Identity_insert before on the same table, but it was because it was taken by the other table. I didn't realized until I review the Error messages one by one. :P :D

https ://dba.stackexchange/questions/12650/why-is-identity-insert-on-only-allowed-on-on-one-table-一次一次