SQL SERVER – Fix : Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

This error occurs when the user has attempted to insert a row containing a specific identity value into a table that contains an identity column. Run following commands according to your SQL Statement. Let us learn about the IDENTITY_INSERT.

Before your SQL Statement:

SET IDENTITY_INSERT <tablename> ON

{YOUR SQL INSERT STATEMENT}
After your SQL Statement:

 SET IDENTITY_INSERT <tablename> OFF

Let me know if this resolves the problem for you. I honestly think identity should be used very carefully and they should not reset without proper purpose.

Here are few additional references for you to follow up on the subject of the identity:

Reference: Pinal Dave (https://blog.sqlauthority.com)

Menu
Exit mobile version