How to insert values into an identity column in SQL Server

Hello friends, today we are going to learn, how we can insert values into an Identity column.

Whenever we set the identity property of a column “On”, we need not to explicitly insert values into that column. SQL Server implicitly insert values into identity column based on incremental value. Here we will see how we can insert values into identity column explicitly. First of all we will create a table with identity column.

Below is the script for creating table with identity column:-

create table Identitytable(id int identity,Name varchar(50))

In table “Identitytable”, I have defined “Id” column as an identity column. Lets insert few rows into this table without explicitly inserting values into “Id” column.

insert into Identitytable(Name) values('Neeraj')

insert into Identitytable(Name) values('Raj')

insert into Identitytable(Name) values('Akshay')

insert into Identitytable(Name) values('Ankit')

insert into Identitytable(Name) values('Vikas')

We will run above script to insert 5 rows into table “Identitytable” and let’s check what is inserted into the table.

In the above image we can see the values for id column(1,2,3,4,5), while we did not explicitly insert the values into the “Id” column. Now let’s try to insert values into “Id” column explicitly.

In the below script, we are trying to insert value “9” into “Id” column explicitly, let’s see what is the outcome of the below insert query.

insert into Identitytable(Id,Name) values(9,'Mamta')

We are getting an error message while inserting value into “Id” column explicitly. Error message states that “Cannot insert explicit value for identity column in table ‘identitytable’ when IDENTITY_INSERT is set to OFF”.To explicitly insert values into identity column, we need to turn on the IDENTITY_INSERT property of the table and then we can explicitly insert values into identity column. Below is the example of doing that.

set identity_insert Identitytable onà Turn on the identity_Insert Property

insert into Identitytable(Id,Name) values(9,'Mamta')

set identity_insert Identitytable offàTurn off the identity_Insert Property

After running the above query, we will check the result.

We can see in the above image value "9" has successfully inserted into column "Id".

Note:- We can have only one identity column per table and at a time, we can enable "IDENTITY_INSERT" property for a single table.

I hope you will find this article helpful.