Insert ID

Question

Can anyone, explain what SET IDENTITY INSERT ON AND OFF does.

Thank you, Chris

Answer 1

Suppose you have a table with an auto-increment / identity column. Normally, you cannot specify values ​​for this column when pasting, because it is automatically populated.

However, if you first call SET IDENTITY_INSERT YourTable ON , you can insert certain values ​​into the identifier column.

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

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

Answer 2

SET IDENTITY_INSERT ON

Allows you to insert explicit values ​​in the table identifier column.

Answer 3

Michael Haren gave you the answer, but I want to reinforce. It is very dangerous to use this command, and you should not use it, with the exception of a few very limited cases. When you use it, autonumbering no longer works for anyone, so all the code to insert records, usually into the database, will fail. This should be done in single-user mode, when possible, or at least after hours. You should not regularly insert records, but only for things such as combining data from one table into a new structure in a database redesign. If you don’t know what you are doing when you use this command, you can royally ruin the integrity of the database data.

Answer 4

chrisprat, while you are registered for a week, and all your activity was to ask five questions that are easy to answer either Google or MSDN.

What?