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.
1) Here is my table structure.
CREATE TABLE Student(
Id INT IDENTITY(1,1),
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.
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