I would like to transfer hundred millions of records between table in different database server. I tried to use DTS export/import and it’s not fast enough for my needs but i forget that there is a BCP command in SQL which is SQL Server format file.
The speed in my local machine is (22795.13 rows per sec)
Sample of the command: (You need to run it in command prompt), you can find bcp.exe in folder (C:\program files\Microsoft SQL Server\100\Tools\Binn) – The 100 is for SQL Server 2008, for SQL server 2005 it’s 90
Sample
1. You need to generate the BCP File from the source table
bcp MyDatabase.dbo.MyTable out C:\BCP_MyTable -n -S localhost -T -e[BCP_MyTable_ERROR]
*Using -T is for trusted connection
2. You need to import the BCP file to the destination table
bcp MyDatabase.dbo.MyTable in C:\BCP_MyTable -n -S localhost -T -e[BCP_MyTable_ERROR]
for further command line reference click here
*UPDATE:
you can configure your server to to reduce the amount of transaction log during bulk copy/insert transaction by executing
EXEC SP_DBOPTION MyDatabase, ‘SELECT INTO/BULKCOPY’, TRUE
bcp MyDatabase.dbo.MyTable in C:\BCP_MyTable /b 20000 -n -S localhost -T -e[BCP_MyTable_ERROR]
What I did was to add extra parameter of /b and the number 20000 (you should play around with the number to see the best one fit your situation) after it means the number of the records per transaction. Please be careful if you don’t put /b parameter, sql server by default will commit all the records at once. but if you put /b parameter then it will commit the transaction per x amount of records specified. If you transfer large data, you shouldn’t commit all at once because it will take sometime. In my case I transferred 18 millions of records
I’d recommend you to save the BCP file and copy the file over if it’s in different server because you need to be careful with the bandwidth of the network and the risk if the network connection is interrupted