January 31, 2007 at 9:09 am
Guys:
I am importing a DTS'd table that comes in as 1MRows and 60 columns each day. The data is imported as Varchar(100) and needs to be "changed" to INT.
At the moment I'm doing this by inserting two unique columns changing from Varchar(100) to BigInt then updating on an inner join between the import table and the archiveal table where these two columns are the same in each table. This was faster than a combined insert of 1MRows and "changing" 60 columns.
Questions are
What is the fastest way to get from Varchar(100) to Int. (I'm using CAST at the moment)
Is it quicker to make the updates in batches of 100,00 rather than one massive one.
General guidance would be really appreciated
January 31, 2007 at 9:15 am
Depends on your server, but batches (if possible) allow you to commit sections of changes. If something happens with the transaction, like log filling, the whole thing rolls back.
I've often found batches quicker as well.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 31, 2007 at 9:27 am
Many thanks
First suggestion noted
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply