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
Posted On : 23 Feb 2013
Total Views : 152,172   
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 (0)
2 JUN
Hadoop Development cum Administration (Online)

7:00 AM-9:00AM IST

Know More
28 MAY
ASP.NET Core with Angular4 Development (Classroom)

10:30 AM-11:30 AM Weekend

Know More
27 MAY
ASP.NET MVC with Angular4 Development (Classroom)

4:00 PM-5:30 PM Weekend

Know More
25 MAY
ASP.NET MVC with Angular4 Development (Online)

09:00 PM-11:00 PM IST(+5.30 GMT)

Know More
20 MAY
Angular2 and Angular4 Development (Online)

08:00 AM-10:00 AM IST(+5.30 GMT) Weekend

20 MAY
NodeJS with Angular4 Development (Classroom)

5:00 PM-6:30 PM Weekend

8 MAY
ASP.NET MVC with Angular4 Development (Online)

07:00 AM-09:00 AM IST(+5:30 GMT) M/W/F

2 MAY
ASP.NET Core Development (Online)

07:00 AM - 09:00 AM IST( TUS, THR)

29 APR
ASP.NET MVC with Angular2 Development (Classroom)

9:30 AM-11:00 AM

22 APR
MEAN Stack 2 Development (Classroom)

11:00 AM-12:30 PM Weekend

10 APR
Master Class ASP.NET MVC 5 with Angular2 Plus Angular4 Development (Online)

09:00 PM - 11:00 PM IST (M/W/F)

25 MAR
Xamarin Forms : Build Cross-platform Apps (Classroom)

8:00 AM-9:30AM Weekend

BROWSE BY CATEGORY
 
 
LIKE US ON FACEBOOK
 
+