using BCP to transfer a large amount of data in SQL Server

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


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


you can configure your server to to reduce the amount of transaction log during bulk copy/insert transaction by executing


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