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

Question

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?

Answer 1

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.)

Answer 2

from https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

so you should set it off before you set it on to another table in your session.