Import Data from Excel Error: Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Last Reply 4 months ago By pandeyism

Posted 4 months ago

when i am import data into table from excel sheet ...it giving error of Cannot insert explicit value for identity column in table 'OrderDetail' when IDENTITY_INSERT is set to OFF. 

I did n't define my first column Name in my excel sheet ,because it number auto generating ,so how can i managed it before importing data into db table 

My table column is  

CREATE TABLE #Orderdetail(ODID INT IDENTITY(1,1) NOT NULL,Codeitem int,orderqty int);

ODID column is auto generated number in my table and i do not mentioned this column in my excel sheet.

so how to avoid this error.

Posted 4 months ago

Hi akhter,

You are inserting different columns from code and your table structure is different than your provided code.

As per the error you are inserting values for ODID that is an identity column.

You can turn on identity insert on the table like this so that you can specify your own identity values.

SET IDENTITY_INSERT OrderDetail ON;
GO
-- Your Insert query.
SET IDENTITY_INSERT OrderDetail OFF;
GO