I have accidentally deleted my record from the table. This record has a key column that is tied to some other tables in database. I have restored the Database and I can access record that I have deleted previously. When I tried to use INSERT SELECT method I was getting message 'An explicit value for the identity column in table 'IEP.dbo.IEP' can only be specified when a column list is used and IDENTITY_INSERT is ON.' Then I run insert on all columns but this first columns where identity is set yo YES in my table design. Now I'm wondering is there any way to update the key? New record automatically generated new key and I want to update that value to the old key. Here is example:
Old/Current table:
i_key
1002491
Temporary/Backup table:
i_key
975217
What would be the best solution to update Current column i_key value with the temporary column i_key value? If anyone can help please let me know. Thank you!
You can do this by issuing an identity insert statement first as this:
SET IDENTITY_INSERT [tablename] ON
go
(do insert)
go
SET IDENTITY_INSERT [tablename] Off
go
Make sure to turn it off afterwards as only one table can have identity insert on at a time.