CODEWITHSUNDEEP
sqlsql-server
Mortalus
Mortalus

Reputation: 10712

IDENTITY INSERT not working with MERGE

I have the following code:

PRINT N'Merging Wide Format Types...'
SET IDENTITY_INSERT WideFormatTypes ON
GO
MERGE INTO WideFormatTypes AS Target
USING (VALUES 
    (1, N'****', NULL),
    (2, N'****', NULL),
    (3, N'******', NULL),
    (4, N'*******', NULL),
    (5, N'******', NULL),
    (6, N'*******', NULL)
)
AS Source (TypeID, TypeName, Description) 
ON Target.TypeID = Source.TypeID
WHEN MATCHED THEN
    UPDATE SET
    TypeID = Source.TypeID,
    TypeName = Source.TypeName,
    Description = Source.Description
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (TypeID, TypeName, Description) 
    VALUES (TypeID, TypeName, Description);

SET IDENTITY_INSERT WideFormatTypes OFF
GO

it returns an error

Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'TypeID'

the table is declared as 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 the problem.. any help ?

Upvotes: 4

Views: 5574

Answers (2)

Kritner
Kritner

Reputation: 13765

You are not able to update the value on an identity column. You'll need to create a new row using the desired value, and remove the existing row.

Upvotes: 2

MatBailie
MatBailie

Reputation: 86775

Try removing the identity column from the UPDATE portion of the MERGE.

IDENTITY_INSERT is, as it says, for INSERTs, not UPDATEs. Also, you include the field in your JOIN as so the UPDATE would never actually change the value anyway.

Upvotes: 9

Related Questions