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

How to insert values to identity column in SQL Server

  Author : Shailendra Chauhan
Updated On : 26 Sep 2016
Total Views : 142,447   
Support : SQL Server 2005,2008,2012
 

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.

YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
14 DEC
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM- 09:00 AM IST

Know More
5 DEC
AngularJS Development (online)

Mon - Fri 6:30 AM-7:30 AM IST

Know More
3 DEC
AngularJS Development (offline)

SAT,SUN     11:00 AM-12:30 PM IST

3 DEC
MEAN Stack Development (offline)

Sat, Sun     (09:30 AM-11:00 AM IST)

26 NOV
ASP.NET MVC with AngularJS Development (offline)

(SAT,SUN)     03:30 PM-05:00 PM IST

24 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     09:30 PM-11:00 PM IST

12 NOV
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     08:00 AM-09:30 AM

3 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     07:30 AM-09:00 AM IST

25 OCT
.NET Development (offline)

Mon-Fri     9:00 AM-11:00 AM IST

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+