Insert Values to Identity Column and Reset in SQL Server

In this blog you will learn how to 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:

CREATE TABLE Employee
    (
        ID int IDENTITY,
        Name varchar(100),
        Address varchar(200)
    )

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.



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.

SETIDENTITY_INSERT Employee ON

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

SETIDENTITY_INSERT Employee OFF

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.

DBCC checkident(Employee, RESEED, 1)

INSERTINTO Employee(Name, Address) VALUES('Priti', 'Bhagalpur')