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.

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.



Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook