SQL Server

Cannot insert explicit value for identity column in table when identity_insert is set to off

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.

 

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.

 

identity_insert is set to off in sql server

 

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.

 

 identity_insert is set to on in sql server

 

Again select the data from DemoIdentityTable  as shown below and you will find id=3 is inserted as shown below

 

Reuse identity value after deleting rows

 

After that I inserted a few records without mentioning the identity column in the T-SQL script as shown below.

 

insert into identity column sql

 

The insertion of a row with old identity value (id=3) didn’t affect the sequence of the identity column as shown below.

 

sequence of identity column

 

In this way you can reuse identity value after deleting rows in sql server table.

Leave a Reply