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

Question

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?

RoMEoMusTDiE · Accepted Answer · 2017-10-11T21:33:36.853

0

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

edited Oct 11 '17 at 21:33

answered Oct 11 '17 at 21:17

RoMEoMusTDiE

4,739
1
17
26

what is strange is that the insert works as it is -- but the front end of the application breaks, but you did give me an interesting idea. – Elizabeth Oct 11 '17 at 21:19
What happens is the script I wrote -- took a ton of data with the column being an identity it simply imported the data into the table, and created for each row a new ID. But the front end of the application the way its built is that it calls its own procedure to create the ID value based on its own rules and I can't use identity (which is stupid) -- so i now have to write a cursor to use the procedure the system does to create all the ID values. – Elizabeth Oct 11 '17 at 21:25
i see what you are trying to do... what sort of application are you working on? – RoMEoMusTDiE Oct 11 '17 at 21:27
what you can do is remove the identity then bulk insert and alter the field to re-instate the identity to ON.. so you can carry on the sequence. i reckon you are trying to preserve the ids which relates to other tables? – RoMEoMusTDiE Oct 11 '17 at 21:28

score 0 · Answer 2 · answered Oct 11 '17 at 21:27

0

If you want to insert a explicit Id in application, you have to set Identity_insert on and insert in one transaction! I don't know, which application you are using, but this rule is universal.

answered Oct 11 '17 at 21:27

Nikolaus

1,859
1
10
16

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

2 Answers2