0

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!

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193

2 Answers2

2

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.

MikeS
  • 1,734
  • 1
  • 9
  • 13
  • Sorry, I missed that you wanted to update the id. This you can't do I don't believe so you'd actually be better off deleting the record with the invalid key and adding inserting with the key you want. – MikeS Jun 21 '17 at 19:50
  • This solution worked for me. I just deleted previously inserted record. Thank you! – espresso_coffee Jun 21 '17 at 20:11
-1

You need to turn the IDENTITY_INSERT option to on before the update, like this:

SET IDENTITY_INSERT Table1 ON
UPDATE [dbo].[Table1]
    SET [TrackingNumber2]=[TrackingNumber]
SET IDENTITY_INSERT Table1 OFF
Reza
  • 18,865
  • 13
  • 88
  • 163
B. Canales
  • 1
  • 1