CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Saurav Tripathy
Saurav Tripathy

Reputation: 21

fill empty column with sequence

Is there any way to fill an empty column with a ascending sequence of numbers starting from 1, without using identity?

I tried the following cursor but it is filling the same value (450) for all rows in the column:

declare cur3 cursor for
select new_id from sheet1$
declare @no int
declare @no1 int
set @no1 = 1
open cur3
fetch next from cur3 into @no
while(@@FETCH_STATUS = 0)
begin
update sheet1$ set new_id = @no1
set @no1 = @no1 + 1
fetch next from cur3 into @no
end
close cur3
deallocate cur3

Upvotes: 0

Views: 2409

Answers (2)

GarethD
GarethD

Reputation: 69769

The problem is that you are missing a WHERE clause from your update query, so each loop is updating all rows. It should be:

UPDATE  sheet1$ 
SET     new_id = @no1
WHERE   New_ID = @No    -- ONLY UPDATE 1 ROW

Just as an aside, and assuming your SQL-Server tag is correct, you could do this without a cursor using the ROW_NUMBER() Function

WITH CTE AS
(   SELECT  New_ID, [RN] = ROW_NUMBER() OVER(ORDER BY New_ID)
    FROM    yourTable
)
UPDATE  CTE
SET     New_ID = RN

EDIT - EXPLANATION

ROW_NUMBER simply provides a sequence of numbers, the Common Table Expression is like a dynamic view:

If you run:

WITH CTE AS
(   SELECT  New_ID, [RN] = ROW_NUMBER() OVER(ORDER BY New_ID)
    FROM    yourTable
)
SELECT  *
FROM    CTE

you should get a better idea of what is being done within the CTE, then the beauty of CTEs is that you can UPDATE them directly without having to reference back to the original table, so Updating the CTE is equivalent to:

UPDATE  yourTable
SET     New_ID = RN
FROM    yourTable
        INNER JOIN
        (   SELECT  New_ID, [RN] = ROW_NUMBER() OVER(ORDER BY New_ID)
            FROM    yourTable
        ) n
            ON n.New_ID = yourTable.New_ID;

Upvotes: 4

Dominic Goulet
Dominic Goulet

Reputation: 8113

Yep, ROW_NUMBER.

Use like the following :

select ROW_NUMBER()OVER(ORDER BY Somefield) As Id
      ,*
  from SomeTable

You can read all about it : http://msdn.microsoft.com/en-us/library/ms186734(v=sql.105).aspx

Upvotes: 1

Related Questions