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

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 Identity 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)

SQL Download, SQL Error Messages, SQL Scripts, SQL Server, SQL Server Security
Previous Post
SQL SERVER – Fix : Error 701 There is insufficient system memory to run this query
Next Post
SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

Related Posts

62 Comments. Leave new

  • Hi,How to display deleted rows in SQL server here no triggers and notification date only three columns in table that is id identity,name varchar(100),age int…..please suggest.

    Reply

Leave a Reply