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 |
|
sanjnep
Posting Yak Master
191 Posts |
 Posted - 2007-11-21 : 14:51:33
|
| I have 2 sql servers and need to tranfer data from one server to another.It has about 4 millions data. Which method is fastest method?Linked server and insert into or data pump task or any other method?Task is simple just :: select * from EmpInfo from server1 and dump into EmpInfo table on server2.Thanks |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-21 : 15:48:38
|
| Probably a data pump is the best way to go. It makes it a lot easier to set a batch (transaction) size, to a reasonable number, like say 50,000 rows. You can also do it with BCP out from server 1 and BCP in to server 2.CODO ERGO SUM |
 |
|
|
maxtin
Starting Member
2 Posts |
Posted - 2007-11-28 : 12:24:54
|
| I have the same situation....but in my case..i need to do this periodically (each midnight) and then clean the source table. Can i still use these methods mentioned? How do i do a data pump or BCP?..i need for info about these. TIA |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-29 : 02:49:52
|
| Still applies, and check books online for bcp syntax. |
|
|
|
maxtin
Starting Member
2 Posts |
Posted - 2007-11-29 : 07:45:38
|
| ive read about BCP...good stuff...the only things i dont like is you have to do it by a shell command. Also, i would like to know exactly which rows failed during a disconnection.Is data pump a feature from SQL Server 2000? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-02 : 01:48:04
|
| What do you mean data pump? Sql2k comes with bcp, dts and bulk insert. |
|
|
|
|
|
|
Fastest method to transfer data