Thanks for the input. It just occurred to me that a better option might be to dump all the data into a temp table which is a replica of table a but without any indexes. I actually used to have to refresh the data in table a on a quarterly basis. I was using a staging table like this, and it didn't take that much time to dump 80-90 million rows and recreate the indexes. I was not doing it this time because I wasn't really thinking. I thought apending data to an extra column would be very straight forward as it appeared to be. But I forgot every update means to delete the old row, reinsert the new row, and on top of that, recreating all the indexes. So I set up an SSIS job to do this dump and it's looking good. Over 3 mil rows have been transferred in 20 minutes to the staging table vs 4 mil records in past 24 hours using the old way through updating. Problem solved! 🙂

While I'm here, I wonder if anybody can enlight me on another puzzle. While I was doing the updates using the following loop, I noticed the tempdb was growing wildly. I thought every loop would wipe the slate clean and shouldn't affect the tempdb size. But looks like I was wrong. Any explanation?

------------------

While exists (select top 1 * from a where URL is null)

Begin

set rowcount 1000

Update a set URL = b.URL

from a

inner join b

on a.ID = b.ID

where a.URL is not null

waitfor delay '00:00:02'

End