Post
Ask Question

Insert Values to Identity Column and Reset 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.

I am going to expose the tips for inserting your own value to this field. It is simple and easy.Please consider following table:

  1. CREATE TABLE Employee  
  2.     (  
  3.         ID int IDENTITY,  
  4.         Name varchar(100),  
  5.         Address varchar(200)  
  6.     )  
After create employee table.

I am trying to insert a record into Employee table with Identity field.

INSERTINTO EMPLOYEE([ID],[Name],[Address])VALUES(1,'AMIT','ALLAHABAD')

It generate an error.

error

Now allow Insert into identity field:

You can insert into identity field by setting IDENTITY_INSERT ON for a particular table as shown:

SET IDENTITY_INSERTEmployeeON

You can also disallow insert into identity field by setting IDENTITY_INSERT OFF for a particular table.

SET IDENTITY_INSERT Employee OFF
lets see how to insert our own values to identity field ID with in the Customer table.
  1. SETIDENTITY_INSERT Employee ON  
  2.   
  3. INSERTINTO EMPLOYEE([ID], [Name], [Address]) VALUES(1, 'AMIT', 'ALLAHABAD')  
  4. INSERTINTO EMPLOYEE([ID], [Name], [Address]) VALUES(2, 'ROHAN', 'ALLAHABAD')  
  5.   
  6. SETIDENTITY_INSERT Employee OFF  
result

Don’t forget to set IDENTITY_INSERT OFF after inserting your own value.

We use this trick when we have deleted some rows from the table and we want the data in a sequence.

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 Employee table ID field from 3 then the new records will be inserted with ID 4,5,6..and so on.
  1. DBCC checkident(Employee, RESEED, 1)  
  2.   
  3. INSERTINTO Employee(Name, Address) VALUES('Priti', 'Bhagalpur')  
result