RE: Tidying data
From: Umut Nazlica (UmutNazlica_at_discussions.microsoft.com)
Date: 02/22/05
- Next message: Griff: "Re: Tidying data"
- Previous message: Tibor Karaszi: "Re: Tidying data"
- In reply to: Griff: "Tidying data"
- Next in thread: Griff: "Re: Tidying data"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 22 Feb 2005 05:37:03 -0800
Did you try inserting trimmed data to new table?
-Script out the table and create new table (do not create any indexes)
- use insert into select (trimmed data)
- rename the table as the original (after renaming the old one)
- create indexes if necessary
"Griff" wrote:
> 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
>
>
>
- Next message: Griff: "Re: Tidying data"
- Previous message: Tibor Karaszi: "Re: Tidying data"
- In reply to: Griff: "Tidying data"
- Next in thread: Griff: "Re: Tidying data"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
