SQL Server: Insert specific value in identity column explicitly

SQL Server by default not allows inserting in column having Identity on, and if you try to insert it will throw error as:

Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF.

But sometime we need to enter a specific value in the column having identity on, for example if we delete specific row identity is not reset.

There is workaround to insert specific value in identity column. We can set IDENTITY_INSERT to ON and after value is inserted we can set this to OFF.

So here we go: First of all Create a table and insert some values in it as:

CREATE TABLE tb_TestIdentity     (         Id int IDENTITY PRIMARY KEY,         Name Nvarchar(100)     ) GO -- Insert two names: INSERT INTO tb_TestIdentity (Name) VALUES ('Sandeep') INSERT INTO tb_TestIdentity (Name) VALUES ('Sachin') GO

And let’s check the record in table as:

You can check Id field have 1 and 2 values and if you insert another record Id will be 3 automatically, but for example you want to skip Id 3 and instead want to insert 4 as next Id value, so here is how we can insert it:

SET IDENTITY_INSERT [tb_TestIdentity] ON INSERT INTO [tb_TestIdentity] ([Id], [Name]) VALUES (4, 'New Name') SET IDENTITY_INSERT [tb_TestIdentity] OFF

And this simple query will do it for you.