Error Cannot insert explicit value for identity column in table table when IDENTITY INSERT is set to OFF

Question

When I execute the following script: 

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

I'm having the following 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.

What is the error about, and how it can be resolved?

Answer 1

Hello @kartik,

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 

Hope this is helpfull!!
Thank you!!