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

Last Reply 27 days ago By pandeyism

Posted 27 days 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.

You are viewing reply posted by: pandeyism 27 days ago.
Posted 27 days 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