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
Cannot update identity column 'aid'.
I expect since I turned
Identity_Insert ON that I can
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
- SQL Server 2008 R2
- Collation SQL_Latin1_General_CP1_CI_AS