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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: