bcp vs. dts, native sql bcp, text etc.?

One of my upcoming projects is going to be to move some data & its
related heavy processing off an production server off to a newer
server, do the processing, and then move the data back. There will be
large amounts of data going out & going in. My question deals with
efficiency of this data movement. I read in BOL that for exporting,
bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
this data was coming out of SQL Server to a flat text file or bcp'ing
over to another SQL instance. So I'm not sure if I should bcp out to
text, then bcp in to my other server...or just bcp directly to the
other server. Also, I suppose I should not even consider DTS, since
bcp is just as fast, and there won't be any significant transformations
on the data? Opinions?BCP is used between a server and a file, not a server and a server.
<unc27932@.yahoo.com> wrote in message
news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> One of my upcoming projects is going to be to move some data & its
> related heavy processing off an production server off to a newer
> server, do the processing, and then move the data back. There will be
> large amounts of data going out & going in. My question deals with
> efficiency of this data movement. I read in BOL that for exporting,
> bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> this data was coming out of SQL Server to a flat text file or bcp'ing
> over to another SQL instance. So I'm not sure if I should bcp out to
> text, then bcp in to my other server...or just bcp directly to the
> other server. Also, I suppose I should not even consider DTS, since
> bcp is just as fast, and there won't be any significant transformations
> on the data? Opinions?
>|||OK - now that I feel like an idiot.....Should I bcp out, then in to
the other SQL Server, or would a DTS data move be more efficient?|||If you are talking about huge amounts of data, that require no
transformations, then probably BCP. Small data/ transformations probably
DTS. Try out both and see.
<unc27932@.yahoo.com> wrote in message
news:1122401357.578369.275800@.o13g2000cwo.googlegroups.com...
> OK - now that I feel like an idiot.....Should I bcp out, then in to
> the other SQL Server, or would a DTS data move be more efficient?
>|||BCP can be made to work between servers too but I came to know its not a
good practice.
Unc,
Refer this article to give you a head start.
http://www.mssqlcity.com/Tips/bulk_copy_optimization.htm
"ChrisR" wrote:
> BCP is used between a server and a file, not a server and a server.
>
> <unc27932@.yahoo.com> wrote in message
> news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> > One of my upcoming projects is going to be to move some data & its
> > related heavy processing off an production server off to a newer
> > server, do the processing, and then move the data back. There will be
> > large amounts of data going out & going in. My question deals with
> > efficiency of this data movement. I read in BOL that for exporting,
> > bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> > this data was coming out of SQL Server to a flat text file or bcp'ing
> > over to another SQL instance. So I'm not sure if I should bcp out to
> > text, then bcp in to my other server...or just bcp directly to the
> > other server. Also, I suppose I should not even consider DTS, since
> > bcp is just as fast, and there won't be any significant transformations
> > on the data? Opinions?
> >
>
>