How to insert values to identity column in SQL Server

Shailendra Chauhan  Print   2 min read  
23 Feb 2013
26 Sep 2016
Intermediate
174K

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.

Take our free skill tests to evaluate your skill!

In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.

Learn to Crack Your Technical Interview

+
+
Accept cookies and close this message