SET IDENTITY_INSERT

This is the seventh post of SET Statement series.

SET IDENTITY_INSERT is one of the most commonly used SET statement. SET IDENTITY_INSERT will be set to ON when you need to be inserted into the identity column of a table explicitly.

CREATE TABLE Department
(DeptID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DepartmentName Varchar(50)
)


INSERT INTO Department
(DepartmentName)
VALUES
('Account'),
('Human Resources')

If you select from the table you will see DeptID column is populated with requital value.

Now what if we have a requirement of inserting deptid explicitly.

INSERT INTO Department
(DeptID,DepartmentName)
VALUES
(5,'Transport')

this will return an error as shown below.

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Department' when IDENTITY_INSERT is set to OFF.

So to avoid this issue, you need to SET the IDENTITY_INSERT property on. This property is valid on for the session which enabled the IDENTITY_INSERT ON.

SET IDENTITY_INSERT  Department ON

INSERT INTO Department
(DeptID,DepartmentName)
VALUES
(5,'Transport')

SET IDENTITY_INSERT  Department OFF

If the value entered to the identity column is greater than the current identity value, after inserting new value value identity will be set to the new value. For example, if the current identity of the table is 5 and if you insert 100 to the identity column, next time you insert data without IDENTITY_INSERT option on, next value for the identity will be 101. However, if you are inserting a value less than the current identity value, current identity value remains.

To perform a IDENTITY INSERT you nee alter permission to the table not the write permission.

Previous posts of this series,

SET IMPLICIT_TRANSACTIONS ON

SET NOCOUNT

SET DEADLOCK_PRIORITY

SET CONCAT_NULL_YIELDS_NULL

SET FORCEPLAN

SET PARSEONLY