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 |
|
Terpfan
Starting Member
4 Posts |
 Posted - 2008-03-10 : 16:53:12
|
| What is the fast way a stored procedure can copy a table from a linked server?I would like to tune this statement, possibly with hints or other logging options. Assume that table_A and table_B have the exact table structure and that I want to preserve table_A and all its indexes and contraints. The table will be truncated before this load, if that helps in any way.insert into table_A select * from OpenQuery(Server,'select * from Table_B') TIA, Mike |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-10 : 16:57:58
|
| You can use export/import wizard or bulkinsert. |
 |
|
|
Terpfan
Starting Member
4 Posts |
Posted - 2008-03-10 : 17:31:55
|
| from a stored procedure? Isn't the bulk insert for data files, rather than database tables? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-10 : 17:47:17
|
quote:
Originally posted by sodeep
You can use export/import wizard or bulkinsert.
That doesn't help him with his problem since he's using a stored procedure and a linked server.Terpfan, it may help to drop the indexes prior to the insert, then add them back after it completes. But I don't see how you are going to speed this up any further besides that.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-10 : 18:14:34
|
| Your first order of business should be to figure out the bottleneck. Is it the SELECT on the remote server, or the INSERT locally, or the network utilization, and so on. Also, you should figure out if selecting all rows over and over and refilling the local table each time is really what you need, or can you just copy updated rows or new rows by utilizing a timestamp or something similar. Finally, be sure that you are not reinventing the wheel doing manually what replication could do for you automatically and probably more efficiently if set up properly.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
|
|
Fastest INSERT