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

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

7 comments
chris 27 Sep 2013 at 5:15 pm

what if each table had an additional Column called ‘junk;
what I would like to do is duplicate the primary key in one table using ‘junk’ as having a different value.
is it possible to duplicate the primary key using a method like this?

chandralekha 13 Feb 2013 at 9:16 am

Tahnk you for sharing your Knowledge! able to insert records in new table

Derek Dieter 21 Feb 2013 at 9:39 pm
s 22 Nov 2011 at 11:31 am

hi

s 22 Nov 2011 at 11:32 am

hi…………..

Abbas 04 Aug 2011 at 10:58 am

My Problem Solved with your solution in sql server 2008 R2 after I changed Read Only Property of database to True and then change to false.

Gabby 10 May 2011 at 1:04 am

Hi Derek,

I am having an errror in my insert statement becuase i am trying to insert a varchar value into a primary key field which is of type int. What should be the best approach on this issue? Thanks

Featured Articles

 Site Author