How To Use SET IDENTITY_INSERT In SQL Server

SQL Server includes IDENTITY property to generate auto-increment numbers. IDENTITY generates incrementing numbers when a record is inserted into a table. Often referred to as a surrogate key, and commonly used as a primary key to have unique incrementing values in a column. While inserting a record in a table, we do not have to specify identity value explicitly, as by default it takes the next value.

IDENTITY( Seed, Increment)

IDENTITY property takes Seed & Increment as arguments to specify the starting number and incremental gap to the next number. For example, IDENTITY (1,1) specifies that the column value will start at 1 and always incremented by adding 1 to the previous value.

This example will create a Monk table with an Identity column. As a rule, without explicitly specifying ID column we have incremental numbers using IDENTITY property.

Example

USE tempdb;

DROP TABLE IF EXISTS Monk;

CREATE TABLE Monk
(
 ID     INT IDENTITY(1,1)
,[Name] VARCHAR(50)
);
GO

INSERT INTO Monk VALUES('Yo');
GO 10

SELECT * FROM Monk;

DROP TABLE Monk;

SET IDENTITY_INSERT [DBName.SchemaName.TableName] ON/OFF

Presumably, in a scenario, we might need to explicitly add values in the IDENTITY type column. SQL Server included this as an option with IDENTITY_INSERT property. Let’s consider we have done a lot of delete operations on a table. Due to this, the identity column has many gaps. To fill these gaps, we are explicitly adding values to the identity type column.

Syntax

SET IDENTITY_INSERT [DBName.SchemaName.TableName] ON/OFF

DbName – In case applying this property to different database. Although optional if you are in the same database.

SchemaName – If table has got schema other than dbo then mandatory to specify this parameter.

TableName – Mandatory to specify this parameter to give name of table with an identity column.

Example

In this example, reusing the previous query. Populating Monk table with 1 to 10 values in the Identity type column. Later deleting values from 3 to 6 thus creating a gap in the Identity column. If we directly try to insert value 3 in identity column then SQL Server will raise an error.

USE tempdb;

DROP TABLE IF EXISTS Monk;

CREATE TABLE Monk
(
 ID   INT IDENTITY(1,1)
,Name VARCHAR(50)
);
GO

INSERT INTO Monk VALUES('Yo');
GO 10--populate 1 to 10 value in Identity column

SELECT * FROM Monk; -- 10 rows

DELETE FROM Monk WHERE ID >= 3 AND ID <= 6;

SELECT * FROM Monk; -- 6 rows

Use SET IDENTITY_INSERT [TableName] ON

To avoid error while inserting explicit value in a table using IDENTITY_INSERT property. We need to wrap Insert statement within IDENTITY_INSERT ON & OFF. Following this, we can easily insert explicit values in the identity type column.

Referring to the rule, we can have only one IDENTITY_INSERT property set to on in a session. In short, If we have set IDENTITY_INSERT ON for a table then we can not use the same property on another table. Until we have turned OFF the previous property. This can result in an error.

In the following query, we are successfully able to insert values in Monk table using IDENTITY_INSERT ON property.

SELECT * FROM Monk;
SET IDENTITY_INSERT Monk ON;
INSERT INTO Monk (ID, Name) VALUES (3, 'Yo'), (4, 'Yo'), (5,'Yo'); --Success
SET IDENTITY_INSERT Monk OFF;
SELECT * FROM Monk;

Forcing Explicit Value Without IDENTITY_ON Property

If we run the following query, we will get an error stating explicit values for identity columns are not allowed unless we specify IDENTITY_INSERT property to ON.

INSERT INTO Monk VALUES (3, 'Yo');

Msg 544, Level 16, State 1, Line 25
Cannot insert explicit value for identity column in table ‘Monk’ when IDENTITY_INSERT is set to OFF.

IDENTITY_INSERT – Single Per Session

In contrast to the use of IDENTITY_INSERT property allowed once per table in a session. If you try to turn ON this property for multiple tables at the same time SQL Server raises an error. In this query, trying to use this property on multiple tables resulted in an error.

SET IDENTITY_INSERT Monk ON;
SET IDENTITY_INSERT Employee ON;
INSERT INTO Monk (ID, Name) VALUES (3, 'Yo'), (4, 'Yo'), (5,'Yo');
SET IDENTITY_INSERT Monk OFF;

Summary

As a result, we have learnt to use IDENTITY_INSERT ON/OFF property. Using this we can explictly insert values to fill the gaps in Identity type column. If you like this post, you may want to explore more on Tech-Recipes Database Archive posts to learn more useful stuff.