If there is already IDENTITY_INSERT property ON for particular table and again you set to ON It will raise error "states SET IDENTITY_INSERT is already ON and reports the table it is set ON".
After Set ON and Insert records we need to set OFF to avoid unnecessary errors.
You need permission to set this property. By default "sysadmin" server role and the "db_owner" and "db_ddladmin" database role and the object owner has permission.
Here is example for this.
In this example we take one database table "product_master". This table has one identity column "product_id" and other column "product_name".
Now we are inserting some records and we can see that it's identity column has serial unique number. After that we delete one records from middle of that records. So now one gap introduce in serial unique number. Now if you want to insert another product it did not generate that missing serial number it will simply generate greater value from existing numbers.
Suppose we have certain situations where we want to insert that particular identity number record. So at that time we can use "IDENTITY_INSERT" property. You can see in screen shot that there is product_id="5" record is deleted and is missing from list. Now we again insert that identity value using "IDENTITY_INSERT".
SQL Query :
SET IDENTITY_INSERT [product_master] ON INSERT INTO product_master (product_id,product_name) values(5,'USB') SET IDENTITY_INSERT [product_master] OFF
Output (Missing Record Id Sequence) :
Output (After inserting Missing Record Id Sequence) :
Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.