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.

SET IDENTITY_INSERT tblident ON 
GO
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

   

Leave a Reply

Your email address will not be published.