SQL Server insert with identity value

By default editing identity column is not allowed in SQL server.
When we try to insert a row by specifying the identity field value, we get an error like this

Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.

Here I will show you how to insert a row by manually specifying the identity field value.

To turn on inserting identity value, the syntax is as follows

SET IDENTITY_INSERT name_of_the_table ON;

now you will be able to execute insert commands using identity value

To turn off this feature use the following syntax

SET IDENTITY_INSERT name_of_the_table OFF;

For example let's say we have a table ProductTypes having two fields ID and Type.

SET IDENTITY_INSERT ProductTypes ON;
insert into ProductTypes([ID],[Type]) values( 4, 'Shirt' );
SET IDENTITY_INSERT ProductTypes OFF;