TRIMing the entire table in one shot
-
I really don't recommend that method. It will run a scan update for EACH COLUMN instead of once for the table. Try that on 5 M rows and 30 columns and see how long the system goes down
. Seriously you could have written that damn code 5000 times in the time you spent trying to find a better way. A programmer should be lazy as to find the easiest way for the server/application to do the job... not to save from typing a few more key strokes. -
You are correct... Didn't notice mention of how large the tables are?
You can rewrite the inner loop to make a single update by continuing to append the set values for each column into a single sql statement.
The id # are in the syscolumns table, but you don't want to trim a numeric field.
-
Thanks guys. So waht should the final code look like after your last suggestion about the inner loop.
-
Set @Sql = 'Update ' + @TableName + ' SET '
while Fetch...
@Sql = @Sql + @Colname + ' = LTRIM(RTRIM(' + @ColName + ')), '
end while
set @Sql = LEFT(@Sql, DATALENGTH(@Sql) - 2)
exec (@Sql)
-
or, without a cursor
SELECT @sql = COALESCE(@sql+',','UPDATE '+@tablename+' SET ')+
COLUMN_NAME+'=LTRIM(RTRIM('+COLUMN_NAME+'))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename
AND DATA_TYPE LIKE '%char'
edited
p.s. maybe some performance gain by only LTRIM char columns, not sure how much you'd gain though
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply