Have you ever received the following Error when developing your ETL or working with data cleanup?

 

Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table 'tblDemo' when IDENTITY_INSERT is set to OFF.

 

This is because the table has a column marked for identity. The best way around this is to use a table option called IDENTITY_INSERT. This will allow you to insert a row into a table including an identity value for troubleshooting, issue resolution, or reseeding the table.

 

In order to do this, you need to enable IDENTITY_INSERT as demonstrated below:

 

SET IDENTITY_INSERT tblDemo ON

INSERT tblDemo(Table1PK, ColumnDescription)

VALUES (3, 'First Row')

SET IDENTITY_INSERT tblDemo OFF

 

Couple of things to point out about using IDENTITY_ INSERT:

- It can only be enabled for one particular table at a time.

- The user issuing the query must own the object in question.

- You must specify the identity column value when this option is enabled.

- If you are inserting a value greater than the current maximum value for that identity, the identity column will be reseeded with that new value.

 

This is a VERY powerful option and can wreck havoc is used improperly. Be very careful if you are ever using this in production.