Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF
I have the below error when I execute the following script. What is the error about, and how it can be resolved?
Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)
Error:
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF.
You're inserting values for OperationId
that is an identity column.
You can turn on identity insert on the table like this so that you can specify your own identity values.
SET IDENTITY_INSERT Table1 ON
INSERT INTO Table1
/*Note the column list is REQUIRED here, not optional*/
(OperationID,
OpDescription,
FilterID)
VALUES (20,
'Hierachy Update',
1)
SET IDENTITY_INSERT Table1 OFF
don't put value to OperationID because it will be automatically generated. try this:
Insert table(OpDescription,FilterID) values ('Hierachy Update',1)
Simply If you getting this error on SQL server then run this query-
SET IDENTITY_INSERT tableName ON
This is working only for a single table of database
e.g If the table name is student
then query look like this:
SET IDENTITY_INSERT student ON
If you getting this error on your web application or you using entity framework then first run this query on SQL server and Update your entity model (
.edmx file
) and build your project and this error will be resolved