Simple way to renumber records in SQL Server
February 17th, 2009Recently 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
You must be logged in to post a comment.