4

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mortalus
  • 10,574
  • 11
  • 67
  • 117

2 Answers2

9

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.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • thanks that is more descriptive than the previous answer ! although i got the same info from there... – Mortalus May 11 '15 at 19:50
2

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.

Kritner
  • 13,557
  • 10
  • 46
  • 72
  • hoooo thanks !!!! i worked in auto pilot mode !!! missde that !!! thank you so much ! was struggling with the inster statement while the problem what with the update !!! – Mortalus May 11 '15 at 19:40