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


Advertisements
Comments
  1. Diwakar Rai says:

    I used this query today….

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s