Add new column to existing table and insert incremental sequence number
Posted on 2009-07-14
We need to add a new column to a table and then populate this column with a sequence number based on some ordering of the current data.
i.e. we have a date in the table which we want to order by and then based on this order assign the correct sequence number hence if you order by date and order by sequence number you get the same order.
I was wondering if anybody knew the fastest way of doing this?
Currently the only option we have found is to take a copy of the table and order the data and then using a for loop insert into the column using a sequence . This seems like a slow method to me considering you can only use a for loop and not bulk operations. We can't use a simple insert statement as the number of rows is 28m.
Does anybody know of a quick way of doing this?