Friday, March 14, 2014

Cannot insert explicit value for identity column in table ' ' when IDENTITY_INSERT is set to OFF.

Replicate the Problem:

create table [dbo].[Color]
(colorkey int identity(1,1) not null,
colorname nvarchar(50) not null,
constraint pk_color_key primary key clustered (colorkey)
);


insert into color (colorname, colorkey) values (  'Black',  2);
insert into color (colorname, colorkey) values (   'Blue' , 3);
insert into color (colorname, colorkey) values (   'Grey' , 4);
insert into color (colorname, colorkey) values (   'Multi' , 5);
insert into color (colorname, colorkey) values (   'Red' , 6);

 
ERROR:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ' ' when IDENTITY_INSERT is set to OFF.
 
PROBLEM:
This error means we are trying to add a value explicitly to a column (insert query), where the database automatically add it.

SOLUTION: 
Execute below command to give up the error.
set identity_insert color on;


Note*- It is not advise to assign value for column, which is set as "IDENTITY". Let the database insert value implicitly.
** color is the table-name (in heading solution)

No comments:

Post a Comment

web stats