Fill blank field with sequential numbers in large table

[jtabio]

Large table (20 millions records) without enumerated field.
I need to create a column, let's call the field: RecNum.
I want to start with the first record in the table, give the RecNum field in that record a number, let's say 1. Then, the second record 2, the third, 3, etc., and continue to the end of the table.

RecNum
1
2
3
...

 

[markros]

One way is to have an identity field. Create a new table with the same structure + identity field and insert all the records (in batches).

I don't see a better / quicker idea.

PluralSight Learning Library

 

[jtabio]

Thank you for your opinion.
That is the aproach I've been using.
Just looking to see if there are any other ways to do it faster.

 

[gbaughma]

What about something like:

UPDATE table SET (RecNum = @@ROWCOUNT)

... not sure if that would work or not.... but I don't see why not....


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[markros]

declare @RecNum int= 0

update MyTable set @RecNum = RecNum = @RecNum + 1



PluralSight Learning Library