SQL increment a number

Question

Problem:

I want to increment a number based on a table. So for example, if a table contains row

1 1 2 3 4 4 4 5

mytable column should increment based on this taking the max(row) + 1 in the above column. So the outcome should look like this:

6 6 7 8 9 9 9 10

This is the code so far:

OPEN cur

DECLARE @WORKING_ON_ID INT
FETCH NEXT FROM cur INTO @WORKING_ON_ID

WHILE @@FETCH_STATUS = 0
BEGIN
                SET @MAX_ID = @MAX_ID + 1

                UPDATE 
                                #WorkingTable
                SET
                                ID = @MAX_ID
                WHERE
                                ID = @WORKING_ON_ID

FETCH NEXT FROM cur INTO @WORKING_ON_ID

END

CLOSE cur
DEALLOCATE cur

Could you please help me in getting a solution to this problem. Thanks!

Answer 1

Wouldn't it be easier to just take the maximum and add it to this ID column? (Remember: the ID column can't be an identity column, otherwise an update will fail)

DECLARE @MAXID INT
SELECT @MAXID = MAX(ID) FROM #WorkingTable
UPDATE #WorkingTable SET ID = ID + @MAXID