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’;

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:

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:

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:

Now let’s insert and check:

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!!

 

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