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 TABLEdbo.MyNames
(
ID intIDENTITY(1,1) NOTNULL PRIMARYKEY,
FName varchar(50) NULL
)
INSERT INTOdbo.MyNames
(
FName
)
SELECT 'Abe'
UNION
SELECT 'Henry'
UNION
SELECT 'Phil'
-- Create YourNames Table with Identity Column
CREATE TABLEdbo.YourNames
(
ID intIDENTITY(4,1) NOTNULL PRIMARYKEY, --Start Incrementing at 4
FName varchar(50) NULL
)
INSERT INTOdbo.YourNames
(
FName
)
SELECT 'Bill'
UNION
SELECT 'Candy'
UNION
SELECT 'Sara'
-- Attempt to Insert MyNames Identity Into YourNames
INSERT INTOdbo.YourNames
(
ID,
FName
)
SELECT
ID,
Fname
FROM dbo.MyNames
-- we get the error message:
Cannot insertexplicit value foridentity column intable'YourNames'whenIDENTITY_INSERT isset toOFF.
-- Attempt to Insert after turning on IDENTITY_INSERT ON