How to ensure that row with same PK inserted replaces old row in MS SQL Server, instead of throwing exception of PK Violation? 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 &amp; 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 &amp; 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?
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   &lt;--- 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!