Copying a large amount of data between to tables

Last Post 14 Jan 2008 11:53 AM by SQLUSA. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
kjmcsd
New Member
New Member

--
11 Jan 2008 09:42 AM
I have two tables that each sit in separate databases on two different servers. What is the best and fastest way to copy data between the two tables?

If I do an export of the data it takes extremely long to copy.
If I do an Select, Insert it takes extremely long.

I know there is a bcp but I'm not all that familiar with it.
kjmcsd
New Member
New Member

--
11 Jan 2008 09:52 AM
About 7 million rows.

I looked at bcp and it seems like bcp cannot copy from table to table. I'll look in books online
nosepicker
New Member
New Member

--
11 Jan 2008 12:58 PM
With a large amount of data, it will usually be faster if you drop the indexes, constraints, keys, and triggers in the target table, and then re-create them after you copy the data. Of course, there's always a bit of inherent risk in doing this if you forget to re-create something.
SQLUSA
New Member
New Member

--
12 Jan 2008 07:28 AM
The simplest is to create a staging table for the delta-s (records you want to move).

I assume you don't want to move the entire table everyday?

You copy over the staging table with DTS. Then run update/synch. In fact the entire process can be a DTS/SSIS package.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
nosepicker
New Member
New Member

--
13 Jan 2008 07:55 AM
quote:

We have some data import operations that originally dropped the indexes for efficiency, but now perform better with them left on during the import because we have transferred so much data now.


That's interesting Pete. My general experience has been, the larger the amount of data, the greater the need for dropping the target indexes. I guess one can't make such a blanket statement, and that there are a lot more factors involved in the copying speed than I know of. So it looks like one has to try different methods to see what works best. Thanks for the knowledge.
SQLUSA
New Member
New Member

--
14 Jan 2008 11:53 AM
If this is recurring synch, log shipping is also a good alternative.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
You are not authorized to post a reply.

Acceptable Use Policy