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

Question

I have below error when i execute the following script;

Could you specify what it is and how it can be resolved?

Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)


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.

Answer 1

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 (OperationID,OpDescription,FilterID) VALUES (20,'Hierachy Update',1)

SET IDENTITY_INSERT Table1 OFF

Answer 2

don't put value to OperationID because it will be automatically generated. try this:

Insert table(OpDescription,FilterID) values ('Hierachy Update',1)

Answer 3

Be very wary of setting IDENTITY_INSERT Table1 ON. This is a poor practice unless the datbase is in maintenance mode and set to single user. This affects not only your insert but those of anyone else trying to access the table.

Why are you trying to put a value into an indentiy field?