Need to update 22,000,000 row table

[Valeda]

I have a 22,000,000 table that has a field which is a concatenation of 3 other fields. I need to extract this information into 3 separate fields. What is the best way to do this without hogging memory and space on the server? Is there a way to loop through? SQL2000 server.

 

[mrdenny]

You can loop through it and process smaller batches of records. Something like this should do the trick. The substring pieces will need to be changed, etc.

declare @continue as bit
set @continue = 1

set rowcount 1000
while @continue = 1
begin
   update table
      set C3 = substring(C1, 0, 10),
       C4 = substring(c1, 11, 7),
       C5 = substring(c1, 18, 3)
   where C3 is null

   if @@rowcount = 0
       set @continue = 0
end

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Valeda]

Hi Mr. Denny,
I am trying your suggestion on a 1000000 file. Do you know if this frees space after it processes a batch of 1000 rows?

 

[mrdenny]

If your database is in simply recovery mode then yes the transaction log will clear as soon as it checkpoints.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Valeda]

I ran using the code above. It took 1 hr 13 minutes to process 1000 rows. This table contains 22,000,000+ rows.

 

[mrdenny]

It won't be fast, but it shouldn't be that slow. If you index the column used in the where it should speed up the query.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog