SQL Server Performance Forum – Threads Archive
IDENTITY_INSERT
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 D2go 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
SET IDENTITY_INSERT t1 ON
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)
SET IDENTITY_INSERT [dbo].[ADS_GetSmartTarrif] OFF check SET IDENTITY_INSERT in BOL
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
]]>