IDENTITY_INSERT | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive


Hi, I am having two DB i.e. D1 & D2 and i am moving data from table D1..t1 to D2..t1 In both the DB structure of t1 is same and in DB d2 i am using below query to get the data Use D2
go Insert into t1 select * from D1..t1 Go
But whenever i am executing above query i am getting below Identity Error. "An explicit value for the identity column in table ‘t1’ can only be specified when a column list is used and IDENTITY_INSERT is ON." I understand from above error that i have set Identity insert explicit ON.So i have executed below command Use D2
go but then also i am getting above error. Please provide your inputs on how to fix the issue. Thanks and Regards
Ravi K
I have done this to success SET IDENTITY_INSERT [dbo].[ADS_GetSmartTarrif] ON
INSERT INTO [dbo].[ADS_GetSmartTarrif] ([Id], [Country], [Rate]) VALUES (1, N’Afghanistan’, 125)
Insert into(columnlist) D2..t1 select (columnList) from D1..t1
where column list excludes identity column
Madhivanan Failing to plan is Planning to fail
Hi Madhivanan, Thanks for your input. I have executed based on your inputs but my problem is half solved. Let me add more to my below issue. Take an scenario where in my t1, I have one Identity column as "ID" with value 1,2,3,4,6,7,8 (5 missing) and now i want similar value to be inserted in my table t1 in D2.If i use your solution then my d2.dbo.t1 table will have value 1,2,3,4,5,6,7 which is wrong. I want similar sequence which is present in table D1.dbo.t1 table. Please provide your inputs. Thanks and Regards Ravi K
Then you need to Set Identity_Insert On that table as told by Dinesh
set identity_insert D2..t1 on
Insert into(columnlist) D2..t1 select (columnList) from D1..t1
set identity_insert D2..t1 off Madhivanan Failing to plan is Planning to fail