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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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