Have you ever deleted a row of data and then needed to put it back due to being told it was the wrong row, but the table had an identity column and will not let you insert the row back? Using the Set Identity_Insert command, you can insert into the table.
Here is a our Traffic_Sources table and we are told we no longer need the “customer referral”, source_id=91 row, so we can delete it.
We delete the row with no issues.
And the row is deleted from the Traffic_sources table.
A couple hours later we are told that they requested the wrong row to be deleted, can we “just put it back” (don’t you love it?) Being the good DBA’s that we are, we made a backup of the data just in case. We try and put the data back, and we receive the following error:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘traffic_sources’ when IDENTITY_INSERT is set to OFF.
Looking at the table definition, we see that the column Source_ID is an identity column and does not allow direct inserts to that column. What do we do now?
We can use the SET IDENTITY_INSERT command to temporary change that mode, so we can insert a row into the table. To read more about the SET IDENTITY_INSERT command, please follow this link:
set identity_insert traffic_sources on
We now can run our insert statement since we allowed inserting into an identity column.
Looking in our Traffic_Sources table, we see that the data is back.
After we are done, we need to be sure to turn off the ability to insert to that field by running the: set identity_insert traffic_sources off command.