[SOLVED] Identity Insert error is being generated on insert (not in code)

Identity Insert error is being generated on insert (not in code)

I'm attempting to build a table with the following code - No errors when I create the table, I can see the struncture and it shows that IDENTITY_INSERT is set to ON.

CREATE TABLE lt_percent_cs
(
    id_key INT IDENTITY PRIMARY KEY,   
    customer_no INT ,
    season INT,
    percentage DECIMAL,

    created_by VARCHAR(15) NULL,
    create_dt DATETIME NULL,
    last_updated_by VARCHAR(15) NULL,
    last_update_dt DATETIME NULL,
    create_loc VARCHAR(16) NULL
) ON [primary]

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].lt_percent_cs 
    ADD CONSTRAINT [lt_percent_created_by] 
        DEFAULT (user_name()) FOR [created_by]
GO

ALTER TABLE [dbo].lt_percent_cs 
    ADD CONSTRAINT [lt_percent_create_dt]  
        DEFAULT (getdate()) FOR [create_dt]
GO

ALTER TABLE [dbo].lt_percent_cs 
    ADD CONSTRAINT [lt_percent_create_loc]  
        DEFAULT [dbo].fs_location() FOR [create_loc]
GO

SET IDENTITY_INSERT lt_percent_cs ON

I get the following error when I attempt to insert data (through application not via code).

Last Error: Database update failed:
dataobject=

sqlca.sqlerrtext=SQLSTATE = 42000
Microsoft OLE DB Provider for SQL Server
Cannot insert explicit value for identity column in table 'lt_percent_cs' when IDENTITY_INSERT is set to OFF.

No changes made to database.

INSERT INTO lt_percent_cs (id_key, customer_no, season, percentage)
VALUES (54891, 80055514, 2017, 50)

sqlca.sqldbcode=544

sqlsyntax:
INSERT INTO lt_percent_cs (id_key, customer_no, season, percentage) VALUES (54891, 80055514, 2017, 50)

row:1 [nvo_ds_database.uf_update.34 (544)]

I should add when I run the script in SQL Server Management Studio, it works without issue and no errors are generated.

INSERT INTO lt_percent_cs (id_key, customer_no, season, percentage) 
VALUES (54891, 80055514, 2017, 50)

Any thoughts?

Solution

Remove id_key from Insert statement since id_key is identity seeded

INSERT INTO lt_percent_cs (customer_no, season, percentage ) VALUES ( 80055514, 2017, 50 )

Otherwise declare id_key as integer Primary Key then Insert should work

INSERT INTO lt_percent_cs ( id_key, customer_no, season, percentage ) VALUES ( 54891, 80055514, 2017, 50 )

Since you want bulk insert data and maintain the identity I can suggest alter id_key to integer primary key then bulk insert then alter field back to identity to ON... going forward you don't need to insert id_key