- 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.
Don't forget to post your thoughts or email me your questions to ajorgensen@pragmaticworks.com. As always, this Blog is to help you better understand the tools at your disposal …
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:
