How to reseed the identity value of a table?


I was working on a project and there was a lookup table in that which needed to be populated and its identity values needed to strictly match with the database at my client’s end. At first when I tried to insert data from another table it turned in to an error, the rows didnt get inserted but the identity value increased due to a lost transaction. So I ran this script in order to reset the indentity value in that table. Please note that to start the identity values from 1 you need to reseed it to zero. The other important thing is that you should delete the rows with a higher identity value because it will not skip that, but it will try to overwrite it which would result in an error.

dbcc checkident (mytableName, reseed, 0)

Advertisements

Author: Imran Akram

A .NET/SharePoint Consultant and a political enthusiast who believes in putting the feet of the elected and/or unelected officials to fire in order to get good governance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s