如果 Identity 不是主鍵，則 IDENTITY_INSERT ON 的 MERGE 不起作用

我經常使用MERGE語句並且對它非常熟悉。現在我遇到了一些表的IDENTITY列不是主鍵的情況。在這種情況下，儘管在合併語句的生成腳本中檢查了標識列的存在並且在identity_insert合併之前顯式打開了標識列，但腳本失敗了。然而它仍然失敗。

我為展示創建了一個較小的範例，該範例也失敗了，並抱怨IDENTITYColumn：

無法更新身份列“幫助”。

我希望自從我轉身後Identity_Insert ON，我可以INSERT或我喜歡UPDATE的列的值。IDENTITY但它不起作用。

這是範常式式碼：

CREATE TABLE [dbo].[tm2]
(
   [id] [int] NOT NULL,
   [aid] [int] IDENTITY(1,1) NOT NULL,
   [txt] [nchar](10) NULL,

   CONSTRAINT [PK_tm2] 
      PRIMARY KEY CLUSTERED ([id] ASC) 
           WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].tm2 ON

MERGE INTO [dbo].tm2 AS Target
USING (VALUES
 (1,2,'qdqewqf'),
 (2,3,'#ED7F00')
) AS Source ([ID], [aid], [txt]) ON (Target.[ID] = Source.[ID])

WHEN MATCHED AND (Target.aid <> Source.aid OR Target.txt <> Source.txt ) THEN
   UPDATE 
   SET
      aid = Source.aid, 
      txt = Source.txt

WHEN NOT MATCHED BY TARGET THEN
   INSERT([ID], aid, txt)
   VALUES(Source.[ID], Source.aid, Source.txt)

WHEN NOT MATCHED BY SOURCE THEN 
   DELETE;

SET IDENTITY_INSERT [dbo].tm2 OFF

技術細節：

• SQL Server 2008 R2
• Collation SQL_Latin1_General_CP1_CI_AS

無法更新標識值。如果為該表打開，則可以插入**它們。IDENTITY_INSERT

這段程式碼展示了它是如何工作的：

USE tempdb;

CREATE TABLE dbo.TestIdentity
(
   ID INT NOT NULL
       IDENTITY(1,1)
   , SomeData VARCHAR(255) NOT NULL
);

INSERT INTO dbo.TestIdentity (SomeData)
VALUES ('This is a test');

--This works

SET IDENTITY_INSERT dbo.TestIdentity ON;

INSERT INTO dbo.TestIdentity (ID, SomeData)
VALUES (1, 'This is a test');

SET IDENTITY_INSERT dbo.TestIdentity OFF;

/*
   This fails with:

   Msg 8102, Level 16, State 1, Line 15
   Cannot update identity column 'ID'.
*/
SET IDENTITY_INSERT dbo.TestIdentity ON;

UPDATE dbo.TestIdentity
SET ID = 2
WHERE ID = 1;

SET IDENTITY_INSERT dbo.TestIdentity OFF;

您可能希望將該IDENTITY列替換為使用手動遞增值的列。如果您使用的是 SQL Server 2012+，則可以使用 aSEQUENCE來填充值。由於您使用的是 SQL Server 2008 R2，因此您需要推出自己的解決方案來生成值來替換標識。這裡詳細介紹了一種這樣的方式。

OUTPUT您可以使用該子句同時刪除該行，然後將其插入具有修改的 ID 值的表中，從而潛在地解決您的問題。但是，這是基於不使用合併構造的。

/*
   This works, but cannot be used with MERGE
*/
TRUNCATE TABLE dbo.TestIdentity;

INSERT INTO dbo.TestIdentity (SomeData)
VALUES ('This is a test');

SET IDENTITY_INSERT dbo.TestIdentity ON;

DELETE
FROM dbo.TestIdentity
OUTPUT 2 /* The new identity value */
   , deleted.SomeData
INTO dbo.TestIdentity (ID, SomeData)
WHERE ID = 1 /* the old identity value */;

SET IDENTITY_INSERT dbo.TestIdentity OFF;

SELECT *
FROM dbo.TestIdentity

引用自：https://dba.stackexchange.com/questions/123208