How to insert values to identity column in SQL Server

Identity field is usually used as a primary key. When you insert a new record into your table, this field automatically assign an incremented value from the previous entry. Usually, you can't insert your own value to this field.

In this article, I am going to expose the tips for inserting your own value to this field. It is simple and easy. Consider you have the following Customer table.

CREATE TABLE Customer
(
 ID int IDENTITY,
 Name varchar(100),
 Address varchar(200)
)

Now, I am trying to insert a record into Customer table with identity field like as then I will get the error message as shown below.

INSERT INTO Customer(ID,Name,Address) VALUES(1,'Shakham','Delhi')

Allow insert into identity field

You can alllow insert to the identity field by setting IDENTITY_INSERT ON for a particular table as shown:

SET IDENTITY_INSERT Customer ON

Disallow insert into identity field

You can also disalllow insert to the identity field by setting IDENTITY_INSERT OFF for a particular table as shown:

SET IDENTITY_INSERT Customer OFF

Insert Value to Identity field

Now, lets see how to insert our own values to identity field ID with in the Customer table.

SET IDENTITY_INSERT Customer ON
INSERT INTO Customer(ID,Name,Address) VALUES(3,'Rahul','Noida')
INSERT INTO Customer(ID,Name,Address) VALUES(4,'Rahul','Noida')
SET IDENTITY_INSERT Customer OFF
INSERT INTO Customer(Name,Address) VALUES('Rita','Noida')

After Inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF.

Note

1. Usually, we use this trick when we have deleted some rows from the table and we want the data in a sequence.

2. After Inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF

Reseed the Identity field

You can also reseed the identity field value. By doing so identity field values will start with a new defined value.

Suppose you want to reseed the Customer table ID field from 3 then the new record s will be inserted with ID 4,5,6..and so on.

--Reseeding the identity
DBCC checkident (Customer, RESEED, 3)
INSERT INTO Customer(Name,Address) VALUES('Geeta','Noida')

What do you think?

I hope you will enjoy the tips while playing with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Further Reading