Please enable Javascript to correctly display the contents on Dot Net Tricks!

How to insert values to identity column in SQL Server

 Print 
  Author : Shailendra Chauhan
Posted On : 23 Feb 2013
Total Views : 155,376   
Updated On : 26 Sep 2016
 

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.



Free Interview Books
 
COMMENTS
19 AUG
MEAN Stack 2 (Online)
5:30 Pm - 7:00 PM IST (+5:30 GMT)
19 AUG
ASP.NET MVC with Angular4 (Online)
10:30 Am to 12:30 PM IST (+ 5:30 GMT)
19 AUG
NodeJS with Angular4 (Classroom)
05:30 PM to 07:00 PM IST (+5:30 GMT)
12 AUG
Angular2 and Angular4 (Online)
08:00 AM - 10:00 AM IST(+5.30 GMT)
12 AUG
ASP.NET MVC with Angular4 (Classroom)
09:30 AM - 11:00 AM IST (+5:30GMT)
4 AUG
NodeJS with Angular4 (Online)
08:00 PM - 09:30 PM IST(+5.30 GMT)
26 JUL
ASP.NET MVC with Angular4 (Online)
07:00 AM - 09:00 AM IST(+5.30 GMT)
22 JUL
MEAN Stack 2 (Classroom)
04:00 PM - 05:30 PM IST(+5:30GMT)
20 JUL
Hadoop cum Administration (Online)
07:00 AM - 09:00 AM IST(+5:30 GMT)
15 JUL
ASP.NET MVC with Angular4 (Online)
03:30 PM - 05:30 PM IST(+5.30 GMT)
1 JUL
ASP.NET MVC with Angular4 (Classroom)
08:00 AM - 09:30 AM IST
1 JUL
MEAN Stack 2 (Classroom)
11:00 AM - 12:30 PM IST(+5:30 GMT)
30 JUN
Xamarin Forms : Build Cross-platform Apps (Online)
09:30 PM - 11:00PM IST(+5:30GMT)
6 JUN
ASP.NET MVC with Angular4 (Online)
09:00 PM-11:00 PM IST(+5.30 GMT)
LIKE US ON FACEBOOK
 
+