How to ensure that row with same PK inserted replaces old row in MS SQL Server, instead of throwing exception of PK Violation?

Question

I'm transferring bulk data from a C# DataTable into a SQL Server DB. The DataTable provides values for all 26 columns except the first column, which is an auto-incremental ID The schema skeleton is as follows:

CREATE TABLE (
ID INT IDENTITY(1,1) PRIMARY KEY
TS_Code INT, -- This value is generated at run-time & is unique for each row
Column2 .....
(Upto 25 columns)
)

I want that if a row is entered with the same TS_Code, it should be replaced with the newly inserted row. I wrote a trigger that would delete the old row & insert new one.

CREATE TRIGGER [Trig_Insert]
ON [dbo].[Input]
INSTEAD OF INSERT
AS
BEGIN
    --To check if unique key already exists in DB

    DECLARE @TS_C BIGINT
    SELECT @TS_C = TS_Code FROM inserted    --TS_Code is unique for each row, but hasn't been set as "UNIQUE"

    --If TS_Code already exists
    IF EXISTS(SELECT * FROM Input WHERE TS_Code = @TS_C)
    BEGIN
        delete from Input
        where TS_Code = @TS_C
    END

    --Insert into [Input] table; get an auto-incremented PK ID assigned to new row
    INSERT INTO Input
    SELECT * FROM inserted
END

However, I get the following error when trying to compile this trigger: "An explicit value for the identity column in table 'Input' can only be specified when a column list is used and IDENTITY_INSERT is ON."

Why is this, when I'm not even sending it explicit Identity values?

Answer 1

To fix that error just list the fields you want to insert. This means, all of the fields but ID.

Solution 1:

Replace

--Insert into [Input] table; get an auto-incremented PK ID assigned to new row
INSERT INTO Input
SELECT * FROM inserted

With:

INSERT INTO Input (TS_Code, column2, column3,..., column25)
SELECT TS_Code, column2, column3,..., column25 FROM inserted;

Notice that in this case your trigger will delete records and you will have holes in your table:

ID      TS_Code       Column2 ....
----------------------------------
1       1001          valueX
3       1003          valueY
4       1002          valueZ   <--- your trigger found a repeated 1002, ID = 2 record is deleted.

If this is what you want, go ahead with Solution 1. Also notice, that your trigger will work only for record by record insert. A bulk insert will call the trigger once and you will need to handle each record using cursors.

Solution2:

If you want to keep the records already found, you probably want to use the MERGE option:

first of all put all the records from C# into a Working table. Lets say table C_Sharp that should look like this:

CREATE TABLE C_SHARP(
TS_Code INT, 
Column2,
...
Column25
)

With that C_SHARP table having all the records, perform a MERGE sentence:

MERGE INTO Input I
   USING (
          SELECT TS_Code, Column2, ... , Column25
            FROM C_SHARP
         ) C
      ON I.TS_Code = C.TS_Code

WHEN MATCHED THEN   
        UPDATE 
           SET Column2 = C.Column2,
               ...,
               Column25 = C.Column25
WHEN NOT MATCHED THEN  
    INSERT (TS_Code, Column2, ..., Column25)  
    VALUES (C.TS_Code, C.Column2, ..., C.Column25)  
END;  

Good luck!