Error Fix: Msg 8101 An explicit value for the identity column in table 'idIn_copy' can only be specified when a column list is used and IDENTITY_INSERT is ON

This is really very well known and famous error when you are trying to insert value in identity column.  If you want to get rid of this error, you have to set “Identity_Insert” property to ON so that you can manually insert Identity value.

Let us see one small example.

--Source table

create table idIn

(

id int identity(1,1),

name varchar(10)

)

go

--destination table

create table idIn_copy

(

id int identity(1,1),

name varchar(10)

)

GO

--insert data into source table

insert into idIn

select 'ritesh' union all

select 'rajan'

GO

--setting Identity_insert property ON

SET IDENTITY_INSERT idIn_copy ON

GO

--insert from source table to destination

insert into idIn_copy

select * from idIn

go

--setting Identity_Insert property to OFF

SET IDENTITY_INSERT idIn_copy OFF

go

One you will run INSERT query after setting IDENTITY_INSERT on, you will get the same error J

You have to give field list in INSERT statement, once you will give it, you will be ok. Have a look:

--setting Identity_insert property ON

SET IDENTITY_INSERT idIn_copy ON

GO

--insert from source table to destination

insert into idIn_copy(id,name)

select id,name from idIn

go

--setting Identity_Insert property to OFF

SET IDENTITY_INSERT idIn_copy OFF

go

This test is perform on SQL Server 2008 but expect to have same result in SQL Server 2005 too.


Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com