SQL Server Scripts : Insert particular value in Identity column in table by using IDENTITY_INSERT property

You can insert value in identity column manually using INSERT statement using "IDENTITY_INSERT" property. You need to set IDENTITY_INSERT property to ON to allow identity insert value.
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) :

 

This is the very useful SQL Server Scripts.

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.