I have the following table with 10 unique rows, BookingID is a FK containing a random number. The number doesn’t need to be in sequence.
BookingID, Description 1000 Foo 3000 Bar 1500 Zoo
I need to insert an sequential index called ID which goes from 1..x
how do I do that in SQL Server 2005? I was thinking to write a cursor and x=x+1 but maybe there is a better way?
This is the result I want
Id, BookingID, Description 1 1000 Foo 2 3000 Bar 3 1500 Zoo
SELECT ROW_NUMBER() OVER(ORDER BY t.bookingid) AS id, t.bookingid, t.description FROM YOUR_TABLE t
id bookingid description ---------------------------- 1 1000 Foo 2 3000 Bar 3 1500 Zoo
To update the existing
id column, 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
BETTER ALTER THAT TABLE ADN ADD A IDENTITY COLUMN LIKE
ALTER TABLE TABLENAME ADD SRNO IDENTITY(1,1)