How to Insert Values into an Identity Column in SQL Server

Identity field is generally used as a primary key. When we insert a new record into our table, this field automatically assign an incremented value from the previous entered value in it. Generally, we can't insert our own value in this field.

In this article, I am going to display the tips for inserting our own value in this field. Let us assume the following Student table.

CREATE TABLE Student
(
 ID int IDENTITY,
 Name varchar(100),
 Class varchar(50)
) 

Now, I am trying to insert a record into Student table with identity field like this then I will get the error message as shown below.

INSERT INTO Student(ID,Name,Class) VALUES(1,'Jassi','fifth')

We can alllow insert to the identity field by setting IDENTITY_INSERT ON for a particular table as shown below:

SET IDENTITY_INSERT Student ON
Now, lets see how to insert our own values to identity field ID in Student table. 

INSERT INTO Student(ID,Name,Class) VALUES(1,'Jassi','fifth')
INSERT INTO Student(ID,Name,Class) VALUES(2,'Sonu','Seventh')

Now, lets see output of Student table.





After Inserting your own value to identity make sure you set IDENTITY_INSERT OFF.

Reset the Identity field

DBCC checkident (Student, RESEED, 1)
Now if we insert any new row in Student table its value start from 2.




That’s it!!…..Happy Programming...