MERGE statement in IDENTITY(1,1)

Question

CREATE Type  SupplierTable_type as table(
    [Supplier_ID] [nvarchar](10) NOT NULL,
    [CounterID] [int] IDENTITY(1,1) NOT NULL,
    [SupplierNotes] [ntext] NULL,
    [GUID] [uniqueidentifier] NULL
 )
GO
--------------------------------------
CREATE PROCEDURE [dbo].[Update_SupplierTable]
      @TblSupplierTable SupplierTable_type READONLY
AS
BEGIN
      SET NOCOUNT ON;
      MERGE INTO SupplierTable c1
      USING @TblSupplierTable c2
      ON (c1.Supplier_ID COLLATE SQL_Latin1_General_CP1_CI_AS)=(c2.Supplier_ID COLLATE SQL_Latin1_General_CP1_CI_AS)
      WHEN MATCHED THEN
      UPDATE SET 
       c1.[Supplier_ID] = c2.[Supplier_ID]
      ,c1.[CounterID]=c2.[CounterID]
      ,c1.[SupplierNotes]=c2.[SupplierNotes]
      ,c1.[GUID]=c2.[GUID]
      WHEN NOT MATCHED THEN
      INSERT VALUES( 
       c2.[Supplier_ID]
      ,c2.[CounterID]
      ,c2.[SupplierNotes]
      ,c2.[GUID]
);
END

Error:

Msg 8102, Level 16, State 1, Procedure Update_SupplierTable, Line 7 [Batch Start Line 57] Cannot update identity column 'CounterID'.

The error is telling you the problem here. What about the error don't you understand? Also, why are you changing the collation of the columns in the `ON`? — Thom A, Nov 27 '20 at 10:28
It looks like you're using that column as the match criteria. Why would you then need to set it? — Ben Thul, Nov 27 '20 at 15:59
Do you actually want to manually insert identity values? Why not allow them to be automatically assigned as the identity column is intended? I can highly recommend reading the official documentation on the merge statement, its very clear with lots of examples. — Dale K, Nov 30 '20 at 05:01
And as for you error "error converting datetime to money" - you can't have matched your insert/update values with the correct column. — Dale K, Nov 30 '20 at 05:12
Please refer to https://stackoverflow.com/questions/19155775/how-to-update-identity-column-in-sql-server — Paul Maxwell, Nov 30 '20 at 05:52
Does this answer your question? [How to update Identity Column in SQL Server?](https://stackoverflow.com/questions/19155775/how-to-update-identity-column-in-sql-server) — Paul Maxwell, Nov 30 '20 at 05:53
@DaleK i face this error .... how its resolve Msg 8101, Level 16, State 1, Procedure Update_SupplierTable, Line 8 [Batch Start Line 59] An explicit value for the identity column in table 'c1' can only be specified when a column list is used and IDENTITY_INSERT is ON. — Adan QUICKFUEL, Nov 30 '20 at 06:47
Why do you want to insert identity values yourself? Thats very unusual, and not the normal way to use an identity column. And if you look at the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15) as recommended, it shows how to explicitly specify the columns you are inserting - which you should **always** do for any insert statement. — Dale K, Nov 30 '20 at 06:49

score 1 · Answer 1 · answered Nov 27 '20 at 10:55

I have no idea why you want to do this. As you have provided no context it is hard to come up with an optimal solution.

That said, use the below if you want to fix the error.

CREATE Type  SupplierTable_type as table(
    [Supplier_ID] [nvarchar](10) NOT NULL,
    [CounterID] [int] IDENTITY(1,1) NOT NULL,
    [SupplierNotes] [ntext] NULL,
    [GUID] [uniqueidentifier] NULL
 )
GO
--------------------------------------
CREATE PROCEDURE [dbo].[Update_SupplierTable]
      @TblSupplierTable SupplierTable_type READONLY
AS
BEGIN

SET IDENTITY_INSERT SupplierTable ON;

      SET NOCOUNT ON;
      MERGE INTO SupplierTable c1
      USING @TblSupplierTable c2
      ON (c1.Supplier_ID COLLATE SQL_Latin1_General_CP1_CI_AS)=(c2.Supplier_ID COLLATE SQL_Latin1_General_CP1_CI_AS)
      WHEN MATCHED THEN
      UPDATE SET 
       c1.[Supplier_ID] = c2.[Supplier_ID]
      ,c1.[CounterID]=c2.[CounterID]
      ,c1.[SupplierNotes]=c2.[SupplierNotes]
      ,c1.[GUID]=c2.[GUID]
      WHEN NOT MATCHED THEN
      INSERT VALUES( 
       c2.[Supplier_ID]
      ,c2.[CounterID]
      ,c2.[SupplierNotes]
      ,c2.[GUID]
);

SET IDENTITY_INSERT SupplierTable OFF;

END

Won't work - this way you can insert identity values, but not update. Updating identity column is plain impossible. — Arvo, Nov 27 '20 at 11:51

MERGE statement in IDENTITY(1,1)

1 Answers1