Insert into Table with an Identity column

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.

 

id1

 

We delete the row with no issues.

id2

And the row is deleted from the Traffic_sources table.

id3

 

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.

 

id4

 

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?

 

id0

 

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:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql

set identity_insert traffic_sources on

id5

We now can run our insert statement since we allowed inserting into an identity column.

id6

 

Looking in our Traffic_Sources table, we see that the data is back.

id7

 

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.

 

id8

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s