When Identity Matters

In a perfect world identity columns should have no real-world meaning and should never be exposed to the user. However, this is not always the case. I've seen systems which use identity columns for booking numbers (which are then exposed to customers) and other systems where records in dimension tables start to be commonly refered to by their surrogate key values, rather than by natural key or name.



As soon as such values start to matter then a potential problem looms if you need to introduce a row with a particular value for the identity column. You can't just go ahead and issue a simple T-SQL INSERT statement, doing so will result in an error.



In order to successfully perform this type of insert you'll need to turn IDENTITY_INSERT on for the table in question. Like so,



SET IDENTITY_INSERT myTable ON



GO



INSERT myTable(ID, Name, Level)

VALUES (2315, 'A new test value', 5)



GO



Once you've finished inserting rows be sure to toggle IDENTITY_INSERT off for the table.



SET IDENTITY_INSERT myTable OFF



GO



Incidently if you are using SSIS to insert data using a Data Flow then take a look at the Advanced options for the SQL Destintion. Ticking the Keep Identity option on this page will allow you to insert values into an identity column.