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 |
|
TallOne
Starting Member
49 Posts |
 Posted - 2008-06-23 : 11:49:16
|
| I'm copying almost all contents of one table from one server/db to another. In relation to speed, what is the difference of using SSIS vs a linked server and sp? I'm going to do the benchmarks, but I'm curious about behind the scenes kinda stuff, theoretically which one if any should be faster and why? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-23 : 11:53:18
|
| SSIS or Export/Import will be faster as it does bulk-copy instead of row-by-row. |
 |
|
|
TallOne
Starting Member
49 Posts |
Posted - 2008-06-23 : 13:31:48
|
| Even if I use TSQLS Statement Task in SSIS, it does a bulk copy rather than row by row?? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-23 : 14:52:30
|
| You can do with Export/Import wizard but you have manually apply contraints to destination table. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-23 : 22:46:48
|
| You can specify batch size in ssis package. |
|
|
|
TallOne
Starting Member
49 Posts |
Posted - 2008-06-24 : 10:27:50
|
| I think the batch size tip was the missing link for me. Thanks for the replies everyone! |
|
|
|
|
|
|
SSIS vs LinkedServer TSQL