August 4, 2011 at 1:27 am
Dear All,
Actually one record has been deleted in customers table in db1 and it's found in db2.
I want to insert that record from db2 to db1 (Customers table ), while running the below query Iam facing the error.
insert into db1.dbo.Customers select * from db2.dbo.Customers where vuserid='us62222'
Error : An explicit value for the identity column in table db1.dbo.Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I have tried to off the IDENTITY_INSERT property on both tables on both dbs..but no use.
Please advise.
Thanks and Regards,
Ravi.
August 4, 2011 at 4:02 am
You have to set identity_insert(Customers) ON and then insert the values.
Look up SET IDENTITY_INSERT on BOL.
-- Gianluca Sartori
August 4, 2011 at 5:11 am
And you would only set it ON for the table you are inserting into, not the one you're reading from. And remember to set it to OFF when you're done.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 4, 2011 at 5:14 am
You can use identity insert ON if you want an exact replica of the row from the second db , however if you want to insert just the data without the need for an exact value from the identity column then simply replace the * in select statement with all column expect the identity column.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply