Identity insert

Question

can anybody please, explain that what does SET IDENTITY INSERT ON AND OFF do.

Thanks, Chris

Answer 1

Suppose you have a table with an auto-increment/identity column. You normally cannot specify values for this column when you do inserts since it is automatically populated.

However, if you call SET IDENTITY_INSERT YourTable ON first, you can insert specific values into the identity column.

I use this most often when merging tables manually--I've never used it in a regularly executed block of code.

Also note that you can only specify this on a single table at a time.

Answer 2

SET IDENTITY_INSERT ON

Allows explicit values to be inserted into the identity column of a table.

Answer 3

Michael Haren has given you the answer, but I want to amplify. It is exceedingly dangerous to use this command and you should not use it except in a few very limited cases. When you use it the autonumbering no longer works for anyone, so all code to insert records normally into the database will fail. It therefor should be done in single user mode whenever possible or at the very least during the off hours. It should not be done to regularly insert records but only for things such as merging data from one table to a new structure in a database redesign. If you do not know what you are doing when you use this command you can royally mess up the data integrity of a database.