copy table from a server to another server

23 Sep 2005 2:39 PM

Kevin

hi guys,
I have a question about copying a table's data from one server to
another server. I want the fastest way to do it. basically, I'm using DTS
wizard to copy but it takes more than 10 minutes because there are a lot of
rows. I wonder if I use BCP by copy data to a file and do bulk insert from a
file, then will it be faster? I 'm not sure because it seems like I'm doing
2 steps instead of 1 step via DTS wizard.

I want to copy data over as fast as possible. Wondering what you guys do in
my situation.

thanks
Kevin

23 Sep 2005 2:45 PM

Yosh

Is the server on the same network? If so, maybe try this:

INSERT [server].[database].[owner].[table]
SELECT [columns] FROM [table]

Show quote

"Kevin" <pearl***@hotmail.com> wrote in message
news:%23O7r4yEwFHA.2864@TK2MSFTNGP10.phx.gbl...
> hi guys,
> I have a question about copying a table's data from one server to
> another server. I want the fastest way to do it. basically, I'm using
> DTS wizard to copy but it takes more than 10 minutes because there are a
> lot of rows. I wonder if I use BCP by copy data to a file and do bulk
> insert from a file, then will it be faster? I 'm not sure because it
> seems like I'm doing 2 steps instead of 1 step via DTS wizard.
>
> I want to copy data over as fast as possible. Wondering what you guys do
> in my situation.
>
> thanks
> Kevin
>

23 Sep 2005 2:54 PM

Kevin

yes, the same network.
but why is your way different from dts or better than DTS?
I don't think it's faster.

Show quote

"Yosh" <yoshi@nospam.com> wrote in message
news:eTpbw1EwFHA.1132@TK2MSFTNGP10.phx.gbl...
> Is the server on the same network? If so, maybe try this:
>
> INSERT [server].[database].[owner].[table]
> SELECT [columns] FROM [table]
>
>
> "Kevin" <pearl***@hotmail.com> wrote in message
> news:%23O7r4yEwFHA.2864@TK2MSFTNGP10.phx.gbl...
>> hi guys,
>> I have a question about copying a table's data from one server to
>> another server. I want the fastest way to do it. basically, I'm using
>> DTS wizard to copy but it takes more than 10 minutes because there are a
>> lot of rows. I wonder if I use BCP by copy data to a file and do bulk
>> insert from a file, then will it be faster? I 'm not sure because it
>> seems like I'm doing 2 steps instead of 1 step via DTS wizard.
>>
>> I want to copy data over as fast as possible. Wondering what you guys do
>> in my situation.
>>
>> thanks
>> Kevin
>>
>
>

23 Sep 2005 2:47 PM

SQL

BCP should be faster

I mean try it out see what the time difference is

http://sqlservercode.blogspot.com/

Show quote

"Kevin" wrote:

> hi guys,
> I have a question about copying a table's data from one server to
> another server. I want the fastest way to do it. basically, I'm using DTS
> wizard to copy but it takes more than 10 minutes because there are a lot of
> rows. I wonder if I use BCP by copy data to a file and do bulk insert from a
> file, then will it be faster? I 'm not sure because it seems like I'm doing
> 2 steps instead of 1 step via DTS wizard.
>
> I want to copy data over as fast as possible. Wondering what you guys do in
> my situation.
>
> thanks
> Kevin
>
>
>

23 Sep 2005 2:56 PM

Kevin

I thought there are some kinds of table setting I can tweak so I can avoid
transaction log. I know one thing, I can remove indexes to speed up the
copying, but what other tricks do you have?

Show quote

"SQL" <S**@discussions.microsoft.com> wrote in message
news:487F4412-7B52-4B5A-A8C4-8B57A6512E15@microsoft.com...
> BCP should be faster
>
> I mean try it out see what the time difference is
>
> http://sqlservercode.blogspot.com/
>
>
>
> "Kevin" wrote:
>
>> hi guys,
>> I have a question about copying a table's data from one server to
>> another server. I want the fastest way to do it. basically, I'm using
>> DTS
>> wizard to copy but it takes more than 10 minutes because there are a lot
>> of
>> rows. I wonder if I use BCP by copy data to a file and do bulk insert
>> from a
>> file, then will it be faster? I 'm not sure because it seems like I'm
>> doing
>> 2 steps instead of 1 step via DTS wizard.
>>
>> I want to copy data over as fast as possible. Wondering what you guys do
>> in
>> my situation.
>>
>> thanks
>> Kevin
>>
>>
>>