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

reprinted from  http://sqlserverplanet.com/sql-server/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity_insert-is-set-to-off/

just for self-learning.

 

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

Posted by  Derek Dieter
on   January 10 2010

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

IDENTITY INSERT ON can only be executed by a user having dbo privilidges

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