10 November, 2010 Roel van Lisdonk 2 Comments 1 category

If you have a table in a Microsoft SQL Server database with an integer column that contains rows and you want to update the existing rows to  contain consecutive numbers, you can use the following T-SQL query:

declare @COUNTER as int
set @COUNTER = 0 -- First number will be [1] 

-- Update the [Number] column, so it contains consecutive numbers
update        [TestTable]
set            [Number] = @COUNTER, @COUNTER = @COUNTER + 1

Tags:
Category: Uncategorized

2 Replies to “Using an T-SQL update statement with a @COUTNER variable to provide consecutive numbers in a column”

  1. Another method to provide row numbers for a table would be:

    ALTER TABLE [TestTable] ADD [RowNumber] INTEGER IDENTITY

    I had fun discovering that method. *wry grin*

  2. Hi;

    Would you be so kind to expand on the methodology above for example it is “looping through the rows” but how does it know?

    Thanks
    Simon

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts