IDENTITY_INSERT in SQL Server

This command is to enable the users to set their own value for IDENTITY Column in case they want to.  Using the setting of SET IDENTITY_INSERT is OFF it is possible to insert our own value into the IDENTITY Column.

Syntax as follows

SET IDENTITY_INSERT tablename ON
SET IDENTITY_INSERT tablename OFF

Let consider the below example

Create table Student with an identity column

CREATE TABLE [dbo].[Student]

(

    [StudentID]       INT NOT NULL IDENTITY(1, 1),

    [StudentName]  VARCHAR(100) NOT NULL

)

Insert record into the table 

INSERT INTO Student(StudentName) VALUES ('Name 1')

INSERT INTO Student(StudentName) VALUES ('Name 2')

INSERT INTO Student(StudentName) VALUES ('Name 3')

INSERT INTO Student(StudentName) VALUES ('Name 4')

INSERT INTO Student(StudentName) VALUES ('Name 5')

Let delete the record no 3 and 4

DELETE FROM Student WHERE StudentID=3

DELETE FROM Student WHERE StudentID=4

we try to insert record at no 3 position

INSERT INTO Student(StudentID,StudentName) VALUES (3,'New Name 3')

it shown error

Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table 'Student' when IDENTITY_INSERT is set to OFF.

First Set IDENTITY_INSERT as ON and insert the record with Identity column. It will allowed to insert the Identity column value

SET IDENTITY_INSERT Student ON

INSERT INTO Student(StudentID,StudentName) VALUES (3,'New Name 3')

INSERT INTO Student(StudentID,StudentName) VALUES (4,'New Name 4')

SET IDENTITY_INSERT Student OFF

SELECT * FROM Student