SET Identity_insert - TSQL Tutorial

SET Identity_insert - allow to be inserted explicit values into the identity column of a table.

SET Identity_insert Syntax:

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF } ;

SET Identity_insert Example:

USE model;
GO
CREATE TABLE Department(
ID INT IDENTITY NOT NULL PRIMARY KEY, Name VARCHAR(250) NOT NULL);
GO
INSERT INTO Department(Name)
VALUES ('Anthropology'), ('Biology'), ('Chemistry'), ('Computer Science'), ('Economics');
GO
DELETE FROM Department WHERE name='Biology';
GO
SELECT * FROM Departments;
GO

IDName
1Anthropology
3Chemistry
4Computer Science
5Economics

USE model;
GO
INSERT INTO Departments (ID, Name) VALUES (2, 'Biology');
GO

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

USE model;
GO
SET IDENTITY_INSERT Departments ON;
GO

Messages
Command(s) completed successfully.

USE model;
GO
INSERT INTO Departments (ID, Name) VALUES (2, 'Biology');
GO

Messages
(1 row(s) affected)