Cannot insert explicit value for identity column in table when identity_insert is set to OFF

Insert Values in Identity Column Explicitly

If we have an identity column on a table then there is no need to provide values for identity column explicitly while inserting values.
However if we try to provide the values for identity column explicitly or want to migrate data from another table to the table which has identity column, then we get the following error “Cannot insert explicit value for identity column in table when identity_insert is set to OFF“.

Inserting records while providing values for identity column is suitable when you do not want to leave the ID column value missing in between.

To insert the values for identity column we need to set it IDENTITY_INSERT ON.
Let’s see with an example. First we create a table with identity column and insert some values. Then we will try to insert values with identity column explicitly.

USE HR
GO
CREATE TABLE tblPerson
(
    ID INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50)
)
GO

Let’s insert some records in tblPerson table. The values will be inserted successfully because we did not specified the values for identity column(ID) explicitly.

USE HR
GO
INSERT INTO tblPerson
(ID, Name)
VALUES
        ('Davio'),
        ('Andrew'),
        ('Sara')
GO

What if we try to insert values for identity column explicitly we get the error as below.

To insert the new records with explicit ID column values we need to SET IDENTITY_INSERT ON and then insert values as shown below.

When we SET IDENTITY_INSERT ON, there must be written column lists in INSERT INTO statement, Otherwise we will get an error stating that An explicit value for the identity column in table ‘tblPerson’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

As soon as we insert the records after setting IDENTITY_INSERT ON, set it OFF.

SET IDENTITY_INSERT tblPerson ON
GO
INSERT INTO tblPerson
(
    ID, Name		
)
VALUES
    (4,'Smith'),
    (5,'John'),
    (6,'Ali')
GO
SET IDENTITY_INSERT tblPerson OFF
GO