MERGE with IDENTITY_INSERT ON does not work if Identity is not the primary key

Question

I frequently use MERGE statements and am quite familiar with it. Now I ran into a situation where some tables have IDENTITY columns that are not the primary key. In this situation the script failed although the presence of identity columns was checked in the generation script for the merge statement and identity_insert explicitly was turned on before the merge. However it still fails.

I created a smaller sample for demo that fails as well complaining about the IDENTITY Column:

Cannot update identity column 'aid'.

I expect since I turned Identity_Insert ON that I can INSERT or UPDATE the value for the IDENTITY column however I like. But it does not work.

Here is the sample code:

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

Technical details:

• SQL Server 2008 R2
• Collation SQL_Latin1_General_CP1_CI_AS

Answer 1

Identity values cannot be updated. They can be inserted if IDENTITY_INSERT is turned on for that table.

This code shows how that works:

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;

You may want to replace the IDENTITY column with a column that uses a manually incremented value. If you were using SQL Server 2012+ you could use a SEQUENCE to populate the values. Since you are using SQL Server 2008 R2, you will need to roll your own solution to generate values to replace the identity. One such way is detailed here.

You could potentially resolve your problem using the OUTPUT clause to simultaneously delete the row, then insert it into the table with a modified ID value. However, this is predicated on not using the merge construct.

/*
    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