Resetting Primary key without deleting truncating table

Question

I have a table with a primary key, now without any reason I don't know when I am inserting data it is being loaded like this

Pk_Col Some_Other_Col
1           A
2           B
3           C
1002        D
1003        E
1901        F

Is there any way I can reset my table like below, without deleting/ truncating the table?

Pk_Col Some_Other_Col
1           A
2           B
3           C
4           D
5           E
6           F

Solution 1

You can't update the IDENTITY column so DELETE/INSERT is the only way. You can reseed the IDENTITY column and recreate the data, like this:

DBCC CHECKIDENT ('dbo.tbl',RESEED,0);    
INSERT INTO dbo.tbl (Some_Other_Col)
SELECT Some_Other_Col
FROM (DELETE FROM tbl OUTPUT deleted.*) d;

That assumes there are no foreign keys referencing this data.

Solution 2

If you really, really want to have neat identity values you can write a cursor (slow but maintainable) or investigate any number of "how can I find gaps in my sequence" question on SO and perform an UPDATE accordingly (runs faster but tricky to get right). This becomes exponentially harder when you start having foreign keys pointing back to this table. Be prepared to re-run this script any time data is put into, or removed from this table.

Edit: IDENTITY columns cannot be updated per se. You can, however, SET IDENTITY_INSERT dbo.MyTable ON;, INSERT a row with the desired IDENTITY value and the values from the other columns of an existing row, then DELETE the existing row. The nett effect on the data being the same as an UPDATE.

The only sensible reason to do this is if your table has about two billion rows and you're about to run out of integers for your identity column. If that's the case you have a whole world of other stuff to worry about, too.

But seriously - listen to @Damien, don't worry about it.