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

10 comments
Angelica 29 Aug 2015 at 6:31 am

Firefox 3.0.14 Windows XPI have realized that car insunarce businesses know the vehicles which are prone to accidents as well as other risks. Additionally , they know what types of cars are inclined to higher risk along with the higher risk they’ve the higher the premium charge. Understanding the straightforward basics of car insunarce just might help you choose the right types of insunarce policy that could take care of your needs in case you get involved in an accident. Thank you for sharing the particular ideas on the blog.

sagar 31 Mar 2015 at 10:38 am

thanku very much your method works…..

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

Insun 29 Aug 2015 at 7:09 am

Firefox 3.5.3 Windows 7Thank you to the blog post. Velupe and I are actually saivng for a new publication on this issue as well as your writing features made you to save all of our money. Your opinions really fixed all the concerns. In simple fact, in way over what there was acknowledged ahead of we came across your amazing blog. I actually don’t have doubts in addition to a troubled mind since you have dealth with the needs in this post. Thanks

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

  • Thanks for visiting!