How to create a column index of a serial number in a data table

Question

I have the following table with 10 unique rows, BookingID is an FK containing a random number. The number does not have to be in sequence.

BookingID, Description
1000       Foo
3000       Bar
1500       Zoo

I need to insert a sequential index named ID that comes from 1..x

How to do this in SQL Server 2005? I was thinking of writing a cursor and x = x + 1, but maybe there is a better way?

As a result i want

Id, BookingID, Description
1   1000       Foo
2   3000       Bar
3   1500       Zoo

Answer 1

It:

SELECT ROW_NUMBER() OVER(ORDER BY t.bookingid) AS id,
       t.bookingid,
       t.description
  FROM YOUR_TABLE t

... will produce:

id  bookingid   description
----------------------------
1   1000        Foo
2   3000        Bar
3   1500        Zoo

To update an existing column id, use:

WITH cte AS (
   SELECT t.id,
          ROW_NUMBER() OVER(ORDER BY t.bookingid) AS rank,
          t.bookingid,
          t.description
     FROM YOUR_TABLE t)
UPDATE cte
   SET id = rank

Answer 2

ADN

ALTER TABLENAME ADD SRNO IDENTITY (1,1)