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.
Usually, we use this trick when we have deleted some rows from the table and we want the data in a sequence.
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.