Inserting explicit values to Identity Column

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

This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s