The error 8101 SQL Server is given below
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.
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
