sql - IDENTITY INSERT not working with MERGE -
i have following code:
print n'merging wide format types...' set identity_insert wideformattypes on go merge wideformattypes target using (values (1, n'****', null), (2, n'****', null), (3, n'******', null), (4, n'*******', null), (5, n'******', null), (6, n'*******', null) ) source (typeid, typename, description) on target.typeid = source.typeid when matched update set typeid = source.typeid, typename = source.typename, description = source.description when not matched target insert (typeid, typename, description) values (typeid, typename, description); set identity_insert wideformattypes off go
it returns error
msg 8102, level 16, state 1, line 1
cannot update identity column 'typeid'
the table declared follows:
create table [dbo].[wideformattypes] ( [typeid] int identity (1, 1) not null, [typename] nvarchar (500) not null, [description] nvarchar (1000) null, primary key clustered ([typeid] asc) );
i can't find problem.. ?
try removing identity column update portion of merge.
identity_insert is, says, inserts, not updates. also, include field in join update never change value anyway.
Comments
Post a Comment