IDENTITY INSERT table property in Microsoft SQL Server

Preamble

The table property for Identity SQL in Microsoft SQL Server is Identity_INSERT. By using this property, we can restore or fill in missing ID values by inserting an explicit value into the autoincrement column with IDENTITY that was previously deleted.

Many SQL Server programmers have probably encountered a scenario where, for whatever reason, some records in the table where the identifier specification is defined need to be restored along with the old values for the identifiers.

The first thing that comes to mind is, of course, to remove the identification, insert the lines with the necessary values, and then restore the identification, but for this, as you understand, you need to perform a lot of manipulations that can affect how quickly the current project is moving along. As a result, they must be completed quickly, and are best done when the database has no active users.

However, there is a way to add values to a column of table identifiers that is actually simpler and, more importantly, correct. The IDENTITY INSERT property will be used in this.

IDENTITY_INSERT property in Microsoft SQL Server

The table property IDENTITY_INSERT enables you to explicitly insert values into the column that contains table identifiers, or the column with IDENTITY. The value of the identifier that is inserted can be less than or more than the current value. For example, if you want to skip a certain range of values, you could do either.

Some considerations must be made when working with this property; let’s look at them:

• If it is necessary to use IDENTITY_INSERT ON for multiple tables in one SQL instruction, you must first set the value to OFF for the table that has already been processed before setting IDENTITY_INSERT to ON for the subsequent table; 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 concurrently;
• The SQL server will automatically use the value that was inserted as the current value if the IDENTITY value to be inserted is greater than the current value, for example, if the next IDENTITY INSERT value is 5 and you use IDENTITY INSERT to insert an ID with a value of 6, then the next ID value will be 7;
• A user must be the owner of the object or have the sysadmin server role, the db_own database role, or the db_ddladm database role in order to use IDENTITY_INSERT.

Example of using IDENTITY_INSERT in T-SQL

Let’s take a look at a scenario where we had to insert a value into a column of identifiers and then need to restore a row that contained that value.

Let’s begin by taking a look at the original data.

Source data
Let’s make a test table with a column of IDENTITY identifiers and fill it with information as an illustration.

Please take note that the example makes use of Microsoft SQL Server 2019 Express.

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;

One record from the table is deleted.

Let’s now eliminate the string whose identifier value is 3.

DELETE Tab_1 WHERE Id = 3;  SELECT * FROM Tab_1;

Finding a deleted record with a specific identifier value and recovering it

We need to restore an entry with the ID 3 because it is missing from our table. Since we are novice SQL programmers, we first try this method.

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

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

Let’s use the IDENTITY_INSERT property and set its value to ON for the TestTable table in order to add a record with this value.

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

As you can see, everything worked out this time, and the record with the recovered identifier value was added.

Note that if the identifier column is the primary key, as it is in our example, the PRIMARY KEY restriction will be triggered when you try to insert an identifier value that already exists. In other words, the unique keys still work.

IDENTITY

You are able to create a column identifier using the IDENTITY attribute. Columns with the numerical types INT, SMALLINT, BIGINT, TYNIINT, DECIMAL, and NUMERIC can have this attribute assigned to them. SQL Server will increase the value of this column in the previous record by one whenever new data is added to a table. The identifier role is typically the same column as the primary key, though this is not always the case.

SQL Tutorial IDENTITY INSERT

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.