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

This error occurs when trying to insert into a column containing an identity. An Identity column is not able to be inserted into without the use of a special command mentioned below. Identity columns are columns that automatically increment when a value is inserted into a row. They are commonly used as primary keys because they guarantee uniqueness.

In order to insert into a table containing an identity column

SET IDENTITY INSERT ‘tablename’ ON

The following example illustrates the error and shows how to successfully insert.

[cc lang=”sql”]
— Create MyNames Table with Identity Column
CREATE TABLE dbo.MyNames
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
FName varchar(50) NULL
)

INSERT INTO dbo.MyNames
(
FName
)
SELECT ‘Abe’
UNION
SELECT ‘Henry’
UNION
SELECT ‘Phil’

— Create YourNames Table with Identity Column
CREATE TABLE dbo.YourNames
(
ID int IDENTITY(4,1) NOT NULL PRIMARY KEY, –Start Incrementing at 4
FName varchar(50) NULL
)

INSERT INTO dbo.YourNames
(
FName
)
SELECT ‘Bill’
UNION
SELECT ‘Candy’
UNION
SELECT ‘Sara’

— Attempt to Insert MyNames Identity Into YourNames
INSERT INTO dbo.YourNames
(
ID,
FName
)
SELECT
ID,
Fname
FROM dbo.MyNames

— we get the error message:
Cannot insert explicit value for identity column in table ‘YourNames’ when IDENTITY_INSERT is set to OFF.

— Attempt to Insert after turning on IDENTITY_INSERT ON
SET IDENTITY_INSERT dbo.YourNames ON

INSERT INTO dbo.YourNames
(
ID,
FName
)
SELECT
ID,
Fname
FROM dbo.MyNames

SET IDENTITY_INSERT dbo.YourNames OFF
[/cc]