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?