How to Turn IDENTITY_INSERT ON SQL Server 2008

IDENTITY_INSERT ON statement that allows us to explicitly specify the value we want to insert into the identity column of a table, instead of depend on the database to automatically generate the next value in the sequence.

Syntax:

SET IDENTITY_INSERT [ [ database_name.] schema_name.] table_name { ON }

Explanation: Values mentioned in square brackets are optional. This syntax is applicable to SQL Server 2000 and later versions. So it covers SQL Server 2008.

Example 1: Insert Explicit Value by Set IDENTITY_INSERT to ON

Let’s consider the situation where we have to insert the students data having id = 10 for some reason. We can not insert the row data directly like this, so in order to insert explicit value we need to set IDENTITY_INSERT to ON.

Query:

SET IDENTITY_INSERT students ON

INSERT INTO students 
(ID, FirstName, LastName, Mentor)
values
(10, 'Piyush','Goyal', 'Virat Gupta'),
(20, 'Pankaj','Varma', 'Prakash Jha'),
(30, 'Sadhana','Chavhan', 'Swapnil Patil')

SELECT * FROM students

Output:

Explanation: We have set IDENTITY_INSERT value of the table students to ON and then inserted the data and notice that we have mentioned the columns list this time and it was not mentioned in the above 2 examples, so whenever we are using identity insert we have to mention the identity columns in the column list and then we can insert whatever value we want to insert even if it is duplicate as identity column does not is not unique unless provided in the definition.

Example 2: Get Back to out Routine Where SQL Server Automatically Insert Values in Id Column

Let’s Insert a new record into the “students” table with the values ‘Madhura‘ for FirstName, ‘Sharma‘ for LastName, and ‘Satish Kumar‘ for Mentor. Ensure that the identity column “ID” is automatically incremented.

Query:

INSERT INTO students values
('Madhura', 'Sharma', 'Satish Kumar')

Output:

Explanation: We faced the error while insert the data normally just like used in example 1, because we have set identity insert to on. So when we want to insert data as per identity definition of seed and increment we have to set the identity insert value to off. We can not set to OFF unless it is already ON.

IDENTITY_INSERT in SQL Server is a valuable tool in SQL Server 2008, allowing us to control how identity values are assigned when inserting new records into a table. IDENTITY_INSERT ON is a Transact-SQL statement that allows us to explicitly specify the value we want to insert into the identity column of a table.

In this article, we will understand How to turn IDENTITY_INSERT on and off using SQL Server 2008 with multiple examples and so on.