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

Question

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.

Answer 1

If you want to insert values for Operation_Id which is an identity column. Then to specify your own identity values, you just turn on the identity insert on the table this way:

SET IDENTITY_INSERT MyTable ON

INSERT INTO MyTable

/*Note: the column list is REQUIRED here, it’s not optional*/

(Operation_ID, OpDescription, Filter_ID)

VALUES (20, 'Hierachy Update',1) SET IDENTITY_INSERT MyTable OFF