Error 8101 SQL Server

The error 8101 SQL Server is given below

1_error 8101 sql server

The above query tries to inserts an explicit value for the identity column without specifying the column name in insert statement and when IDENTITY_INSERT is OFF. Thus the SQL Server throws the error 8101 telling that explicit value for identity column can only be specified when a column list is used or IDENTITY_INSERT is ON.

Thus to specify explicit value for identity column, SET IDENTITY_INSERT to ON and specify the column list in insert statement as shown below.

INSERT INTO tblident(sno,col1) values (100,'A')

The above query sets IDENTITY_INSERT for tblident and inserts an explicit value of 100 in an identity column. The output from above query is shown below.

2_error 8101 sql server

Another thing to note is that the IDENTITY_INSERT is set for a session for a particular table. If we try to execute the insert statement in a new query window it will fail with error 8101.


Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *