How to insert value in an auto incremented column in SQL

Sometimes we have situation to insert value in an auto incremented column. Let's discuss this with a simple example.

Example

1) Here is my table structure.

CREATE TABLE Student(
Id INT IDENTITY(1,1),
Name VARCHAR(10)
)

2) And I have some entries in my table.

3) Now if  I delete some entries and insert new record then new record will come with Id = 6. I have deleted record with Id = 3.

4) Now if I want to insert new record with Id = 3 it will give error as Id column is auto incremented. To do this we have to set  IDENTITY_INSERT to ON and after insert we can set it OFF to disallow manual insert.

Syntax

SET IDENTITY_INSERT TableName ON
INSERT INTO TableName (Col1, Col2...) VALUES (Val1, Val2...)
SET IDENTITY_INSERT TableName OFF

SET IDENTITY_INSERT Student ON
INSERT INTO Student (Id, Name) VALUES (3,'Aneesh')
SET IDENTITY_INSERT Student OFF