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. [cc lang=”sql”] — 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 [/cc]