As we all know, Identity column(s) gets their values automatically when record is being inserted into the table. The values in the idenity column are added as per the definition i.e. seed & increment value of that identity column. So while inserting values into the table having one or more identity column(s), we must skip those columns as they are getting their incremented values automatically.
But sometimes, we require to insert some specific values explicitly into the identity columns, which by default, Identity column will not allow. So to insert desired value into identity column. first we need to disable the identity column, insert the desired value into it & enable back the identity column to its normal behaviour.
Disabing the identity column (making insert ON):
SET Identity_insert TableName ON
Insert the desired values into the table. But while inserting the identity value column name must be specified & in the ‘( )’.
INSERT TableName (IDCol1) VALUES (200)
Now we can enable the Idenity column & disable the explict insertion.
SET Identity_insert TableName OFF