Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF.
-
Posted on January 10, 2010 by Derek Dieter
-
1
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 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
- Comments (RSS)
- Trackback
- Permalink
Tahnk you for sharing your Knowledge! able to insert records in new table
Great!
hi
hi…………..
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.
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
