17 million rows to copy BCP or BULK INSERT or DTS?

Question

17 million rows to copy BCP or BULK INSERT or DTS?

quackhandle1975

SSChampion

Points: 11055
More actions
June 23, 2010 at 8:01 am

#98762

Hi,
I am looking at copying a vast amount of data between SQL 2000 servers (SP4).
The source table has 615 million rows (don't ask, I didn't create it!) and takes time to return any more than 1000 rows and I have a script (below) that returns a result set of 17 million rows that have to be copied to onto another sql server table. I am not sure of which copy method to use as I have not copied that vast amount of rows before. Which one would perform best out of bcp, DTS or BULK INSERT?
DECLARE @SD smalldatetime
DECLARE @ED smalldatetime
SET @SD = '20090301' -- do a month at a time (batch it)
SET @ED = '20090401'
SELECT* FROM transactionitems NOLOCK - this will be changed to select all named cols
WHEREbusinessdate >=@SD AND businessdate < @ED
the data to copy does not contain any BLOBs or TEXT/IMAGE data.
Any ideas?
Thanks in advance
qh
[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

quackhandle1975 SSChampion Points: 11055 More actions · Answer 1

Found this out in the end.

DTS data copy with transformations bombed out after and hour so went with BCP file out from source db - which created a 4GB .dat file - then BULK INSERT back in to destination db - 18m rows copied in 4 minutes!

Well done me! 😉

fc

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

quackhandle1975 (6/24/2010)
Found this out in the end.
DTS data copy with transformations bombed out after and hour so went with BCP file out from source db - which created a 4GB .dat file - then BULK INSERT back in to destination db - 18m rows copied in 4 minutes!
Well done me! 😉
fc

Thanks for the feedback on this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eddie 52254 Valued Member Points: 52 More actions · Answer 3

Could you please post an example of your Bulk Insert statement? Also did you have any clustered indexes?

Thanks.