An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON.

Inserting explicit value for identity column in a table can not be done directly as identity column is bound to rule of incrementing them with specified number. If you try to insert value explicitly you encounter following error message.

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'TableName' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So when you need to insert explicit value to identity column in a table you must set IDENTITY_INSERT ON for that table. Lets see following example.

Create table with following script.

Create Table Department_Master 
(
Id Int Identity(1,1),
DepartmentName Varchar(60),
IsActive bit default 1,
CreatedOn Datetime Default Getdate()
)
Go

I have Department_Master where “Id” column is an identity column which automatically inserts values into this column. Lets see what happens when you try to insert ID column value explicitly.

Try Following Insert Statement.

Insert Into Department_Master(Id,DepartmentName,IsActive,CreatedOn)
Values(1,'Biology',1,GETDATE())
Go

Output :

You can see the output.

Now to overcome this issue you must set Identity_Insert On for table specified. Let do that and check insert.

Set Identity_Insert Department_Master On
Go
Insert Into Department_Master(Id,DepartmentName,IsActive,CreatedOn)
Values(1,'Biology',1,GETDATE())
Go
Set Identity_Insert Department_Master Off
Go

Here I have set Identity_Insert On Before inserting the values and set Identity_Insert off after inserting the value. So you Set it off as next values should come automatically as defined in Identity Rule for that column.

Also you must note that in a database you can only set Identity_Insert On for a single table only. If you try to set Identity_Insert On for more then one table you might encounter following error.

Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'TableName’. Cannot perform SET operation for table 'TableName'.

So Make sure you set Identity_Insert Off once you have inserted Id value explicitly by setting Identity_Insert On.