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:

1_SQL_Server_What_is_IDENTITY_INSERT

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:

2_SQL_Server_What_is_IDENTITY_INSERT

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:

3_SQL_Server_What_is_IDENTITY_INSERT

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

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

14 Comments on “What is IDENTITY_INSERT?”

  1. Dear Piyush,

    how can we catch a Identity value after inserting the rows in to table having a Identity column and using that result in the another Table as reference key

    to same same package. I dont want use oledb command it cause problem during the error handling. please guide me any better solution

  2. Hi Piyush,

    I am bigginer in Sql server. I have table having round abt 2lacs rows n whn i m updating some rows from that table it gives me timeout error. Please suggest some solution.

  3. As I set identity _insert on I get message that entered data is nly for read only and the colour of data is different , what should I do? ?

Leave a Reply

Your email address will not be published. Required fields are marked *