IDENTITY INSERT table property in Microsoft SQL Server

IDENTITY SQL – The table property in Microsoft SQL Server is IDENTITY_INSERT. Using this property, we can insert an explicit value into the autoincrement column with IDENTITY that was previously deleted, i.e. fill in or recover missing ID values.

Probably, many SQL Server programmers had a situation when in the table in which the identifier specification is defined, for some reason, some records are removed, and then there is a need to restore these records, and with the values of the old identifiers.

The first thing that comes to mind is, of course, to remove the identification, insert the lines with the required values, and then restore the identification, but for this, as you understand, you need to perform a lot of manipulations that can affect the progress of the current work, so they must be done quickly, and better at a time when the database does not have any working users.

However, there is actually a simpler and most importantly correct way to insert values into a column of table identifiers. This is to use the IDENTITY INSERT property.

IDENTITY_INSERT property in Microsoft SQL Server

IDENTITY_INSERT is a table property that allows you to insert explicit values into the column of table identifiers, i.e. into the column with IDENTITY. The value of the inserted identifier can be either less than the current value or more, for example, to skip a certain interval of values.

When working with this property, it is necessary to take into account some nuances, let’s consider them:

• The IDENTITY_INSERT property can only take ON for one table in a session, i.e. IDENTITY_INSERT cannot be set to ON for two or more tables in a session simultaneously. If it is necessary to use IDENTITY_INSERT ON for several tables in one SQL instruction, you must first set the value to OFF for the table that has already been processed, and then set IDENTITY_INSERT to ON for the next table;
• If the IDENTITY value to be inserted is greater than the current value, the SQL server will automatically use the inserted value as the current value, i.e. if, for example, the next IDENTITY INSERT value is 5, and you use IDENTITY INSERT to insert an ID with a value of 6, then automatically the next ID value will be 7;
• In order to use IDENTITY_INSERT, a user must have the appropriate rights, i.e. to be the owner of the object or to be part of the sysadmin server role, the db_own or db_ddladm database role.

Example of using IDENTITY_INSERT in T-SQL

So, let’s look at a situation where we had to insert a value into a column of identifiers, let’s say a row with that value has been deleted and we need to restore it.

Let us start by looking at the source data.

Source data
For example, let’s create a test table with a column of IDENTITY identifiers and fill it with some data.

Note! The example uses the Microsoft SQL Server 2019 Express version.

CREATE TABLE Tab_1(
     Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
     TextData_1 VARCHAR(50) NOT NULL
   );

   GO

   INSERT INTO Tab_1 (TextData_1)
     VALUES ('Str_1'),
            ('Str_2'),
            ('Str_3'),
            ('Str_4'),
            ('Str_5');

    GO

   SELECT * FROM Tab_1;

Deletion of one record from the table

Now let’s delete the string where the identifier value is 3.

DELETE Tab_1
   WHERE Id = 3;

   SELECT * FROM Tab_1;

Recovering a deleted record with a certain identifier value

We do not have an entry with Id = 3 in our table and we need to restore it with Id = 3, for this purpose we, as beginners SQL programmers, try it this way first.

INSERT INTO Tab_1 (Id_1, TextData_1)
     VALUES (3, 'Str_3');

As you can see, we failed to insert such a string, SQL Server returned the error “It is impossible to insert an explicit value for the column of identifiers in the table”.

To add a record with this value, let’s use the IDENTITY_INSERT property, i.e. set its value for the TestTable table to ON.

SET IDENTITY_INSERT Tab_1 ON;
   
   INSERT INTO Tab_1 (Id_1, TextData_1)
     VALUES (3, 'Str_3');

   SELECT * FROM Tab_1;

This time everything was successful and the record with the recovered identifier value was added, as you can see.

Note! If the identifier column is the primary key, as in our example, then when you try to insert an identifier value that already exists, the PRIMARY KEY restriction will trigger, in other words, the unique keys continue to work.

IDENTITY

The IDENTITY attribute allows you to make a column identifier. This attribute can be assigned to columns of numerical types INT, SMALLINT, BIGINT, TYNIINT, DECIMAL and NUMERIC. When adding new data to a table, SQL Server will increment the value of this column in the last record by one. Typically, the identifier role is the same column that is the primary key, although in principle this is not necessary.

SQL Tutorial – IDENTITY INSERT