sql-servert-sqlidentity-column

When should I use SET IDENTITY_INSERT <tablename> OFF function?

To insert values into the identity column manually, I use:

SET identity_insert product ON

INSERT INTO product (PID, ProdName, Qty, Unitprice) 
VALUES (10, 'soap', 5400, 22)

Firstly I have deleted the 10th row/record, then I have used this command to insert the identity value and record it manually. this command is inserting the record. It's fine

Where should I write the command SET identity_insert product OFF? And what is the use of the SET identity_insert product OFF command?

Solution

  • From the documentation:

    At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

    So, if you are in a batch where you want to override the auto-generated identity values on two different tables, you would set the first one to OFF before setting the second one to ON.

    (Also, like a lot of things, if you change something from the default, it's never a bad idea to change it back.)