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!

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: