Insert Rows in between a SQL Server Table with Identity Column

I have seen a lot of users asking this question – “I have accidentally deleted a row in a Table with Identity Column. How do I insert that row again?”

Let us assume that we have a table with ‘CustId’ as the Identity Column. The row for CustId 18 has got deleted by mistake. Now if you go ahead and insert the row for CustID=18 (specifying the value of the identity column explicitly) as shown below:

INSERT INTO YourTableName(CustId, FirstName, LastName)
VALUES (18, 'Paul', 'Adams')
GO

SQL Server throws an error as shown below:

Msg 544, Level 16, State 1, Line 1

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

To insert and specify the value in an Identity Column, use the following query:

SET IDENTITY_INSERT YourTableName ON

INSERT INTO YourTableName(CustId, FirstName, LastName)
VALUES (18, 'Paul', 'Adams')
GO

SET IDENTITY_INSERT YourTableName OFF

You will now be able to insert details for CustId=18 successfully!

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook