0

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?

  • 3
    The SELECT * includes the identity column. Specify an explict column list to avoid the error. Also, this trigger has a bug in that it will only handle singleton inserts. Consider MERGE instead to handle multiple row inserts, insert new rows, and update existing rows. – Dan Guzman Jul 6 '18 at 11:54
2

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!

  • Solution 1 is not complete. Your suggestion fixes one issue but the OP's trigger will fail for any INSERT that tries to insert more than 1 row. – ypercubeᵀᴹ Jul 6 '18 at 15:59
  • You are right, @ypercubeᵀᴹ, I was missing the fact that this is a bulk copy... In that case, I only recommend the merge option. However, you can still use triggers in bulk copies including some controls row by row using cursors... thanks for the feedback – AMG Jul 6 '18 at 17:00
  • Thank you so much. I'll use the Merge approach bcoz, yes, I'm using Sqlbukcopy.WriteToServer(DataTable) method. Just one more point to clear: are you saying that I should first transfer my data to C_Sharp Table in SQL, then execute a sort of Stored Proc. to run the Merge commands? Wouldn't that double the time, bcoz I'd be transferring data twice, once in temporary C_Sharp table, then in actual Input table? – Raphael Nazirullah Jul 7 '18 at 12:43
  • Mostly, yes. Do that. execute the bulk copy to c_sharp table, then process the data with that merge solution, then truncate the c_Sharp table, so the table will be ready for the next time. Maybe you want to put the logic in a SP and handle the whole process with a scheduled SSIS. – AMG Jul 9 '18 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.