问题描述
我真的复习了几次,这就是我要问的原因;寻找指导...
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)
我不断收到此错误:
有人知道为什么吗?在数据库级别进行的任何设置都可以否决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
这篇关于SQL Server错误:“无法为标识列插入显式值"即使我将IDENTITY_INSERT设置为ON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!