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