By setting this ON, will allows us to insert explicit values like missing ones into a identity column. Its syntax:
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }
We can set IDENTITY_INSERT to ON on single table in a session. It throws an error, if we try to set it ON on more than one table. We can use it to insert missing values/explicit values into identity column.
Example:
CREATE TABLE IDENTEST(empID int IDENTITY, name varchar(20))
INSERT INTO IDENTEST(name) values('ee')
INSERT INTO IDENTEST(name) values('ee1')
INSERT INTO IDENTEST(name) values('ee2')
DELETE FROM IDENTEST WHERE name='ee1'
SET IDENTITY_INSERT IDENTEST ON
-- Will be success, since it is ON
INSERT INTO IDENTEST values(1,'ee1')