How To INSERT Values Explicitly in Identity Column in SQL

Friends,

In this post, we will see how can we insert explicit values in an Identity column in a table in SQL Server. Normally, the data in IDENTITY column should not be entered manually, but there are certain circumstances where you want to insert custom data in the IDENTITY column.

If you try entering data in IDENTITY column, you get the following error –

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'MyTbl' when IDENTITY_INSERT is set to OFF.

To resolve this error, you need to execute the following command on your SQL Server.

   SET IDENTITY_INSERT MyTbl ON
   GO

Executing the above command tells SQL to allow insertion in Identity columns of Table MyTbl. Be sure to change the table name in the query before executing on your SQL Server.

To Turn off insertion in IDENTITY columns, you can use the following command –

   SET IDENTITY_INSERT MyTbl OFF
   GO

Hope you like this post! Keep learning & sharing! Cheers!