WikiDBA

Sometime SQL Server error msg 8101 occurred, when anyone is trying to insert a new record into a table that contains an identity column without specifying the columns in the INSERT statement and trying to assigning a value to the identity column instead of letting SQL Server assign the value. Error displays as

Server: Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table “Table_Name” can only be specified when a column list is used and IDENTITY_INSERT is ON.

 The solution for above error is , we should include SET IDENTITY_INSERT ON,

Example :

SET IDENTITY_INSERT Table_name ON
Go
Insert into Table_Name (Col1,Col2,Col3,Col4)
Select Col1,Col2,Col3,Col4 from Any_Table_Name
Go

SET IDENTITY_INSERT Table_name OFF
Go