Cannot insert explicit value for identity column in table when identity_insert is set to off
In this post I will discuss how to insert a row with old or previous identity value which was deleted in sql server table or in other words how to reuse identity value after deleting rows in SQL Server.
Let us consider a table named DemoIdentityTable as shown in the image below. This table has two columns id and name . Column named id is an Identity column. Here id =3 is already deleted. I want to reinsert id=3 in an identity column.
When I tried to insert id=3 in an identity column , I got an error . “Cannot insert explicit value for identity column in table when identity_insert is set to off” , This is shown in the image below.
It means you cannot insert values in an identity column because by default IDENTITY_INSERT is OFF of a table in sql server.
The image shown below sets IDENTITY_INSERT to ON. It means now you can insert values in an Identity Column. After inserting the data again set IDENTITY_INSERT to OFF because this will reset to the default option of the table.
Again select the data from DemoIdentityTable as shown below and you will find id=3 is inserted as shown below
After that I inserted a few records without mentioning the identity column in the T-SQL script as shown below.
The insertion of a row with old identity value (id=3) didn’t affect the sequence of the identity column as shown below.
In this way you can reuse identity value after deleting rows in sql server table.