Re: Tidying data

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/22/05 

Date: Tue, 22 Feb 2005 14:32:43 +0100

How about one simple UPDATE statement instead of looping?

UPDATE tbl
SET colname = RTRIM(colname)

200000 rows should be doable in a transaction, but it might be worth splitting this up in several
UPDATE statements, divided by some range in the WHERE clause. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Griff" <Howling@The.Moon> wrote in message news:uX3k2DOGFHA.3908@TK2MSFTNGP12.phx.gbl...
>I have a database table with >20,000 rows and >50 fields, ~45 of which are VARCHARS.
>
> The data in this database is populated from a fixed-width text file using a process that is 
> outside of my control.  However, this results in many of the fields having leading and/or trailing 
> spaces.
>
> I have to find a way to tidy this data up....
>
> I adopted the tactic of getting all the table's data back as a disconnected recordset and 
> iterating through every row & field.  If the type is VARCHAR and the value is not NULL, then I 
> simply update the field's value with the trimmed value.
>
> Iterating through this is extremely quick.
>
> However, the UpdateBatch command is EXTREMELY SLOW.  So slow that it takes ~ 5 minutes to update ~ 
> 4000 rows.
>
> Any alternative tactics I could use?
>
> Thanks
>
> Griff
>

Relevant Pages

  • Re: Tidying data
    ... How about one simple UPDATE statement instead of looping? ... 200000 rows should be doable in a transaction, but it might be worth splitting this up in several ... > Iterating through this is extremely quick. ... > Any alternative tactics I could use? ...
    (microsoft.public.sqlserver.server)
  • Re: computational model of transactions
    ... It seems to me easy enough to tell if an UPDATE statement references ... the appropriate choice of transaction isolation level. ... transactions to obtain a shared lock, but not the exclusive lock required to ...
    (comp.databases.theory)
  • Re: column update order
    ... Robert Klemme wrote: ... The default transaction isolation level does not enforce that SELECTs, ... consistency but about column values referenced in an UPDATE statement. ... indicated that Oracle ensures read consistency within a transaction - ...
    (comp.databases.oracle.misc)
  • Re: deadlocks between single update statements ?
    ... Assume a transaction A with a single update statement updating many ... Assume another transaction B also with a single update, ... But how do I impose an order in an update statement? ...
    (comp.databases.oracle.server)
  • Re: computational model of transactions
    ... It seems to me easy enough to tell if an UPDATE statement references ... The first UPDATE statement above does not, ... the appropriate choice of transaction isolation level. ... in two separate concurrent transactions? ...
    (comp.databases.theory)