Simple way to renumber records in SQL Server

Recently I needed to renumber a column in a database.  Specifically, I needed to be able to take a Report Number series that started with 1 (1, 2, 3, 4) and change the start number and renumber all subsequent Report Numbers (e.g, change above to 14, 15, 16, 17).

I thought I’d probably be able to use a table variable with an identity column – hoped I wouldn’t have to use a cursor.  I did a quick Google search, to see how other people handled similar things and hopefully get some examples.

I saw the table variable solutions I expected, but then came across a post containing this really nifty bit of code that did the trick in four lines – very slick.  Here’s the example as it appeared in the post:

declare @variable int
set @variable = 0
update <tablename>
SET @variable = <columnname> = @variable + 1 

and here’s how I implemented it, along with a WHERE statement so that I only renumber the Report Numbers associated with a particular Job (@ReportNum is the new starting number, and is a parameter fed in to the stored procedure):

declare @counter int
set @counter = @ReportNum – 1
update tblReport
set @counter = ReportNumber = @counter + 1
where JobID = @JobID

Works like a charm.  I repeat – very, very slick:)

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s