Identity Insert is a SQL Server attribute used when you want to insert explicit values into the identity column in a table. In SQL Server, by default, you cannot insert your own value to the identity column. But, in certain circumstances, you want to insert your own values to the identity column and that is when Identity Insert comes in.
Identity Insert allows you to do this. Before understanding how to use Identity Insert, let’s first understand what an identity column is.
CREATE TABLE Employee
(
ID int IDENTITY(1,1),
Name varchar(255),
Salary decimal
)
Here, ID is an identity column. When you insert a new record, SQL Server auto-increments the ID.
The Problem
Suppose you are migrating data from an old database. In the old database, there were ID’s maintained. You have a similar structure in the new database too. Now, while migrating, you want to keep the same old ID’s. But SQL Server won’t allow it by default because ID is the identity column.
The Solution to the Problem
This is when you can use Identity Insert. Here’s a step-by-step guide on how to use it.
Steps to Use Identity Insert
First, you need to set the Identity Insert ON for that specific table like shown below:
SET IDENTITY_INSERT Employee ON
Once you have turned it ON, you can insert your own values to the identity column.
Now, insert your own values.
INSERT INTO Employee(ID, Name, Salary) VALUES(1, 'John', 50000)
Then, you need to set the Identity Insert OFF for that table like shown below:
SET IDENTITY_INSERT Employee OFF
Functions Involved
SET IDENTITY_INSERT is a function in SQL Server that is used to insert explicit values into the identity column.
A quick word of caution: please handle this feature with care because it can break the identity of the sequence, hence it should be used judiciously.
Similar Libraries
While SQL Server uses IDENTITY_INSERT for managing identity columns, different DBMSs have their similar concept. For instance, MySQL AUTO_INCREMENT attribute also allows to set an auto-increment field in a database. Oracle’s equivalent functionality is provided by a combination of sequences and triggers.
Identity Insert is a powerful feature provided by SQL Server. It is very useful when performing tasks such as data migration or copying data from one database to another. However, it must be used carefully to avoid misuse and errors in data integrity. Keep exploring and coding!
