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.