IDENTITY_INSERT in SQL Server

In this SQL Server tutorial, we will learn how to the IDENTITY_INSERT in the SQL Server. We will learn why we use the IDENTITY_INSERT property in SQL Server 2019.

In this article, we will learn how to use the IDENTITY_INSERT property on the SQL Server table. We will also learn about the topics related to this property.

• How to check identity_insert is on or off in SQL Server 2019
• IDENTITY_INSERT on in SQL Server
• IDENTITY_INSERT off in SQL Server
• Set IDENTITY_INSERT on for all tables
• SQL Server set IDENTITY_INSERT in a stored procedure
• SQL Server set IDENTITY_INSERT on permanently
• Cannot insert when IDENTITY_INSERT is set to off SQL Server
• Set IDENTITY_INSERT on not working
• How to check identity_insert is on or off in SQL Server

IDENTITY_INSERT in SQL Server

We can use IDENTITY_INSERT to explicitly insert a value in the identity column in a table explicitly.

In SQL Server, we use IDENTITY columns to generate key values for the newly inserted records. When we define this IDENTITY column, the SQL Server database engine automatically assigns the identity value to the records once we specify the starting value and increment value in the IDENTITY function while creating the table.

But what if you want to insert a value manually in the IDENTITY column of a table while inserting a record. In that case, you have to turn the IDENTITY_INSERT property on for the particular table. Otherwise, you will face an error.

Read: SQL Server Row_Number

How to check identity_insert is on or off in SQL Server

In this section, you will learn how to check if the IDENTITY_INSERT option is ON or OFF. You will also see an example where we have implemented this.

• You can check the IDENTITY_INSERT status of your table with the following script:

DROP TABLE IF EXISTS #GetIdentityInsert
DECLARE
     @dbname sysname,
     @schemaname sysname,
     @table sysname,
     @IdentityInsert VARCHAR(20),

     @OtherTable nvarchar(max),
     @DbSchemaTable nvarchar(max),

     @ErrorMessage NVARCHAR(4000),
     @ErrorSeverity INT,
     @ErrorState INT,
     @ErrorNumber INT,
     @object_id INT;

	SET @dbname=<database name>
	SET @schemaname=<schema name>
	SET @table=<table name>
    SET @DbSchemaTable = @dbname + '.' + @schemaname + '.' + @table

    SET @object_id = OBJECT_ID(@DbSchemaTable)

    BEGIN TRY

        SET @object_id = OBJECT_ID(@DbSchemaTable)

        IF OBJECTPROPERTY(@object_id,'TableHasIdentity') = 0
        BEGIN
            SET @IdentityInsert = 'NO_IDENTITY'
        END
        ELSE
        BEGIN
            -- Attempt to set IDENTITY_INSERT on a temp table.

            CREATE TABLE #GetIdentityInsert(ID INT IDENTITY)
            SET IDENTITY_INSERT #GetIdentityInsert ON

            -- It didn't fail, so IDENTITY_INSERT on @table must set to OFF
            SET @IdentityInsert = 'OFF'
        END
    END TRY


    BEGIN CATCH

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorNumber = ERROR_NUMBER();

        IF @ErrorNumber = 8107  --IDENTITY_INSERT is already set on a table
        BEGIN
            SET @OtherTable = SUBSTRING(@ErrorMessage, CHARINDEX(char(39), @ErrorMessage)+1, 2000)
            SET @OtherTable = SUBSTRING(@OtherTable, 1, CHARINDEX(char(39), @OtherTable)-1)

            IF @OtherTable = @DbSchemaTable 
            BEGIN
                -- If the table name is the same, then IDENTITY_INSERT on @table must be ON
                SET @IdentityInsert = 'ON'
            END
            ELSE
            BEGIN
                -- If the table name is different, then IDENTITY_INSERT on @table must be OFF
                SET @IdentityInsert =  'OFF'
            END
        END
        ELSE
        BEGIN
			SET @ErrorNumber=130001
            RAISERROR (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState);
         
        END


    END CATCH
PRINT(@IdentityInsert)

• In the above code, just add the database name, schema name, and the name of the table whose IDENTITY_INSERT status you want to check.
• Here we are creating a temporary table and try to turn the IDENTITY_INSERT option to ON.
• We know that only one table in a session can have this property on. Therefore, if your table’s IDENTITY_INSERT option is ON, it cannot be turned ON for another table in the same session.
• We are making the use of Exception Handling mechanism to find out the status.
• In our example, the database name is master, schema name is dbo and the table name is IdentityTable.
• Also, we will set the IdentityInsert option to on. The code now will be:

DROP TABLE IF EXISTS #GetIdentityInsert 
SET IDENTITY_INSERT dbo.IdentityTable ON
DECLARE
     @dbname sysname,
     @schemaname sysname,
     @table sysname,
     @IdentityInsert VARCHAR(20),

     @OtherTable nvarchar(max),
     @DbSchemaTable nvarchar(max),

     @ErrorMessage NVARCHAR(4000),
     @ErrorSeverity INT,
     @ErrorState INT,
     @ErrorNumber INT,
     @object_id INT;

	SET @dbname='master'
	SET @schemaname='dbo'
	SET @table='IdentityTable'
    SET @DbSchemaTable = @dbname + '.' + @schemaname + '.' + @table

    SET @object_id = OBJECT_ID(@DbSchemaTable)

    BEGIN TRY

        SET @object_id = OBJECT_ID(@DbSchemaTable)

        IF OBJECTPROPERTY(@object_id,'TableHasIdentity') = 0
        BEGIN
            SET @IdentityInsert = 'NO_IDENTITY'
        END
        ELSE
        BEGIN
            -- Attempt to set IDENTITY_INSERT on a temp table.

            CREATE TABLE #GetIdentityInsert(ID INT IDENTITY)
            SET IDENTITY_INSERT #GetIdentityInsert ON

            -- It didn't fail, so IDENTITY_INSERT on @table must set to OFF
            SET @IdentityInsert = 'OFF'
        END
    END TRY


    BEGIN CATCH

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorNumber = ERROR_NUMBER();

        IF @ErrorNumber = 8107  --IDENTITY_INSERT is already set on a table
        BEGIN
            SET @OtherTable = SUBSTRING(@ErrorMessage, CHARINDEX(char(39), @ErrorMessage)+1, 2000)
            SET @OtherTable = SUBSTRING(@OtherTable, 1, CHARINDEX(char(39), @OtherTable)-1)

            IF @OtherTable = @DbSchemaTable 
            BEGIN
                -- If the table name is the same, then IDENTITY_INSERT on @table must be ON
                SET @IdentityInsert = 'ON'
            END
            ELSE
            BEGIN
                -- If the table name is different, then IDENTITY_INSERT on @table must be OFF
                SET @IdentityInsert =  'OFF'
            END
        END
        ELSE
        BEGIN
			SET @ErrorNumber=130001
            RAISERROR (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState);
         
        END


    END CATCH
PRINT(@IdentityInsert)

This is how to check identity_insert is on or off in SQL Server 2019.

IDENTITY_INSERT on in SQL Server

In this section, we will learn how to set the IDENTITY_INSERT property ON in SQL Server.

• You can set the IDENTITY_INSERT property ON by using the following syntax:

SET IDENTITY_INSERT <table name> ON;
GO

• Let us understand this concept with an example.
• Suppose you have created a table with an identity column.

USE [master]
GO
DROP TABLE IF EXISTS dbo.IdentityTable

CREATE TABLE dbo.IdentityTable(
EmpID int IDENTITY(1,1),
EmpName nchar(20),
EmpDep nchar(20))

INSERT INTO dbo.IdentityTable
	  (EmpName, EmpDep)
VALUES('John', 'Finance'),
	  ('Lana', 'Marketing'),
	  ('Remo', 'Sales'),
	  ('Greta', 'Sales'),
	  ('Jonathan' ,'Finance')
SELECT * FROM dbo.IdentityTable

• The starting value and the increment value are defined in the IDENTITY function for the EmpID column. Therefore, the value in the EpmID will be automatically inserted in an incremental manner.
• Delete any row from the table.

USE [master]
GO
DELETE FROM dbo.IdentityTable WHERE EmpID=3
SELECT * FROM dbo.IdentityTable

• Now you can see there is a missing value in the EmpID column. If you try to insert a value at the missing place you will get an error. For example:

USE [master]
GO
INSERT INTO dbo.IdentityTable
VALUES(3,'Christiano','Sales')

• To resolve this issue, use the IDENTITY_INSERT property.

USE [master]
GO
SET IDENTITY_INSERT dbo.IdentityTable ON;

• Now try to insert the same row as you were trying to insert previously.

USE [master]
GO
INSERT INTO dbo.IdentityTable(EmpID, EmpName,EmpDep)
VALUES(3,'Christiano','Sales')
SELECT * FROM dbo.IdentityTable

• Make sure to list the column names while inserting the row. Otherwise, you will still face the same error.

• This time, you successfully inserted the value in the identity column.

Note: The value in the identity column will not increment if the IDENTITY_INSERT option is turned on. Therefore, it is a good approach to turn this option off after inserting the value explicitly.

Hence, you might have understood how you can use the IDENTITY_INSERT property to insert a value in the identity column explicitly.

Read: SQL Server Add Column + Examples

IDENTITY_INSERT off in SQL Server

In this section, you will understand how to set the IDENTITY_INSERT option off in the SQL Server.

If you want that no one should insert an explicit value in the identity column of a table, you can set the IDENTITY_INSERT option to off as:

SET IDENTITY_INSERT <table name> OFF;
GO

• In the above section, we created a table dbo.IdentityTable and set the IDENTITY_INSERT option on.
• If we want to set this option to OFF, we can do it as:

USE [master]
GO
SET IDENTITY_INSERT dbo.IdentityTable OFF;
GO

• Once you have turned the IDENTITY_INSERT option OFF, you cannot insert explicit values in the identity column of the table.
• Also, the value will be set automatically by increment in the identity column if you try to insert a new record.
• You should always use this option once you have inserted the records.

Set IDENTITY_INSERT on for all tables

When you set the IDENTITY_INSERT option ON for a table, you cannot use this option in another table in the same session. In other words, you can use set this option to ON for only a single table at a time.

You will get an error that the other table is using this option if you try to set this option to ON in more than one table. It will also tell you the name of the table which is using this option.

Therefore, you should always set this option to OFF after inserting the rows.

SQL Server set IDENTITY_INSERT on permanently

When you set the IDENTITY_INSERT option to ON, it remains ON until you explicitly turn it off.

So there is no need to make it permanent, as it is already permanent. However, you should not do this. When you turn this option ON, you should turn this OFF also, after you have inserted a record successfully in the table.

SQL Server set IDENTITY_INSERT in a stored procedure

In this section, you will understand how you can use the IDENTITY_INSERT option in a stored procedure.

• Suppose you have created a stored procedure to insert values in a table and you want to explicitly insert a value in the identity column of the table.

USE [master]
GO
CREATE PROCEDURE dbo.insertIdentity @empID int, @empName nchar(20), @empDep nchar(20)
AS
BEGIN
SET IDENTITY_INSERT dbo.IdentityTable ON;

INSERT INTO dbo.IdentityTable(EmpID, EmpName, EmpDep)
VALUES(@empID,@empName,@empDep)
SELECT * FROM dbo.IdentityTable

SET IDENTITY_INSERT dbo.IdentityTable OFF;
END

• The above stored procedure will set the IDENTITY_INSERT option to ON for the table dbo.IdentityTable.
• Then it will insert the values passed to it as the input parameters in the dbo.IdentityTable table.
• Then the procedure will set the IDENTITY_INSERT option to OFF and display all the records of the table.
• Now execute the above stored procedure.

USE [master]
GO
EXEC dbo.insertIdentity 8, 'Heath', 'Finace'

Hence, you mught have learned how we can use the identity insert option in a stored procedure.

Read: SQL Server drop table if exists

Cannot insert when IDENTITY_INSERT is set to off SQL Server

You will encounter this error if you are trying to insert a value in the identity column of a table. If you are using an identity column in a table, you cannot insert a value explicitly in the identity column.

The error will come as “cannot insert when identity_insert is set to off sql server“.

• You might have faced the error that is shown in the image below:

• In the above image, EmpID is the identity column. When we tried to explicitly insert a value, it threw an error.
• If you want to explicitly insert a value in an identity column you have to set the IDENTITY_INSERT option to ON.
• To set this option to on, write the following code:

USE [master]
GO
SET IDENTITY_INSERT <fully qualified table name> ON;

• In our case, we wrote:

USE [master]
GO
SET IDENTITY_INSERT dbo.IdentityTable ON;

• Now if you try to insert a record, you will not face the same error.
• Therefore, use the above code snippet just before the INSERT statement.

USE [master]
GO
SET IDENTITY_INSERT dbo.IdentityTable ON;
INSERT INTO dbo.IdentityTable(EmpID, EmpName, EmpDep)
VALUES(7,'Nany','Sales')
SET IDENTITY_INSERT dbo.IdentityTable OFF;

• If you still face this error, there might be a problem with your session. Restart the SQL Server service and the SQL Server Management Studio.

This way we can fix, cannot insert when identity_insert is set to off sql server error.

Read: MySQL vs SQL Server – Key Differences in Details

Set IDENTITY_INSERT on not working

In this section, we will discuss the various issues regarding the IDENTITY_INSERT option. You can try the following solutions if the IDENTITY_INSERT option is not working:

• Make sure that while inserting a new record when the IDENTITY_INSERT option is ON, specify the column names in the INSERT statement.
• If you have not specified column names in the INSERT statement, you will get the following error:

• Therefore, column names must be specified in the INSERT statement.
• For example, the above INSERT statement can be written as:

USE [master]
GO
SET IDENTITY_INSERT dbo.IdentityTable ON;
INSERT INTO dbo.IdentityTable(EmpID, EmpName, EmpDep)
VALUES(7,'Nany','Sales')
SET IDENTITY_INSERT dbo.IdentityTable OFF;

• You can try to restart the SQL Server and the SQL Server Management Studio if the problem still exists. If required, you should also try to restart your machine.

You may like the following sql server tutorials:

• Exception Handling in SQL Server

Thus, at the end of this article, you might have learned about the identity_insert statement in SQL server, its use cases, errors faced, and more.

• What is identity_insert in SQL Server
• How to check identity_insert is on or off in SQL Server
• IDENTITY_INSERT on in SQL Server
• IDENTITY_INSERT off in SQL Server
• Set IDENTITY_INSERT on for all tables
• SQL Server set IDENTITY_INSERT in stored procedure
• SQL Server set IDENTITY_INSERT on permanently
• Cannot insert when IDENTITY_INSERT is set to off SQL Server
• Set IDENTITY_INSERT on not working
• How to check identity_insert is on or off in SQL Server