Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GhantaBro
Posting Yak Master
215 Posts |
 Posted - 2008-03-25 : 20:21:53
|
| I have a huge table with 407 columns and around 90 million records.I need to run an update on 10 columns..updates as case, trimming, replace.....What do you guys suggest? Should I run select into or run an update query that runs all night.?Thanks! |
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-03-26 : 03:40:37
|
| What do u mean by select into? |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-26 : 03:44:09
|
Update in batches (1000 or 10000 records at a time) or the chances are that your log file will grow immensely.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-03-26 : 11:43:39
|
| You could do as Peso suggested, which is a great option (but I think I'd use a larger batch size).Another option is to select into a new table and transform the data at that time. Then drop/rename the existing table and rename the new table to take its place. Or you could use DTS/SSIS to pump data to a new table, etc. |
|
|
|
PABluesMan
Starting Member
26 Posts |
Posted - 2008-03-26 : 11:52:32
|
| The problem with using SELECT..INTO is that it puts exclusive table locks on the system tables; this could have serious repercussions elsewhere in the database. That being said, I would still use SELECT..INTO, but I would make sure that it's running off-hours and that all users are notified beforehand that some serious s*** will be happening on the server at that time and they shouldn't get ticked off if their performance grinds to a halt.I geek, therefore I am |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2008-03-28 : 15:46:09
|
| If you run updtes in batches and useset xact_abort on -- rollback and bail on error set implicit_transactions off -- ensure we're running under the default (autocommit) modesay for some reason you have to kill the update, will it try to rollback all the batches or just the last batch? Coz it will take a long time if it tries to rollback all the batches. |
|
|
|
|
|
|
select into or Update