What is IDENTITY_INSERT?

Hi Folks,

What is IDENTITY_INSERT?

I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion.

So in the figure below, CustomerID is an identity column:

Now, let say I have a requirement to insert a CustomerID with value as ‘10’, just below of this value. How can I perform that?

There are two ways:

1. Either you turn off the Identity property by going into design, and opting for column properties, i.e., using GUI. After inserting that value, again making it ON, so that it will continue incrementing it.
2. Or using T-SQL code, which I will explain you below-

Will insert a CustomerID with value as ’10’;

SET IDENTITY_INSERT dbo.Customers ON;

This ON means that it temporarily turns off the identity column and permits the insertion of data into identity column explicitly. So let’s try to insert:

INSERT INTO dbo.Customers
VALUES (10, 'John')

It will throw an error;

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table ‘dbo.Customers’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

And also it is necessary to specify the attributes name for each column:

INSERT INTO dbo.Customers(CustomerID, CustomerName)
VALUES (10, 'John')

The result will be:

So, now once you are ready to continue the CustomerID after value ‘10’, you can turn it off, and bring it back to the normal state:

SET IDENTITY_INSERT dbo.Customers OFF;

Now let’s insert and check:

INSERT INTO dbo.Customers
VALUES ('Kapil')

The result will be like:

As you can understand that now the value starts incrementing from 10 onwards.

Well this was all about IDENTITY_INSERT, and will continue posting on other topics as well. So be tuned!!!

And also comments on this!!

Move and manage your SQL database in the cloud by renting virtual servers and find out more about Azure managed services. Slash IT costs of your organization by getting virtual desktops with the best citrix xendesktop pricing plans from CloudDesktopOnline.

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook