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
0 comments:
Post a Comment