SQL Server Planet

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.

-- 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