How to insert a value into Auto-increment Identity Column in SQL Server.

Suppose we have a table in which a column has Auto-incremented and its value automatically increases on every row. This increasing is depends on the seed and incrementing value.
Let us create a table with an auto-increment identity column :

CREATE TABLE
[TableName]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NULL
)
ON [PRIMARY];

And now add some data in this table :

INSERT [TableName] ([UserName]) VALUES ('abc');
INSERT [TableName] ([UserName]) VALUES ('def');
INSERT [TableName] ([UserName]) VALUES ('ghi');
INSERT [TableName] ([UserName]) VALUES ('jkl');

If you check all data in this table by using this query :

SELECT * FROM [TableName];

you will find that the ID column contains value from 1 to 4.
Now let us delete a row which ID has 2

DELETE FROM [TableName] where ID = 2;

Now If you again check all data in this table by using this query :

SELECT * FROM [TableName];

There is a gap in between ID 1 and 3.
If you will try to add a value explicitly to the identity column to fill up the gap using the statement :

INSERT [TableName] ([ID], [UserName]) VALUES (2, 'xyz');

You will get an error

“Cannot insert explicit value for identity column in table ‘TableName’ when IDENTITY_INSERT is set to OFF”

If you want to insert a value explicitly into an Identity column, use the code below to do so :

SET IDENTITY_INSERT [TableName] ON
INSERT [TableName] ([ID], [UserName]) VALUES(2, 'xyz')
SET IDENTITY_INSERT [TableName] OFF

Here Identity_Insert to ON removes this auto-increment check.
Once you have inserted the record, set it back to OFF again for saving again auto-increment column data.