Can BCP copy data directly from table to table?

Question

I've got a situation where I need to copy several tables from one SQL Server DB to a separate SQL Server DB. The databases are both on the same instance. The tables I'm copying contain a minimum of 4.5 million rows and are about 40GB upwards in size.

I've used BCP before but am not hugely familiar with it and have been unable to find any documentation about whether or not you can use BCP to copy direct from table to table without writing to file in between.

Is this possible? If so, how?

EDIT: The reason we're not using a straightforward INSERT is because we have limited space on the log drive on the server, which disappears almost instantly when attempting to INSERT. We did try it but the query quickly slowed to snail's pace as the log drive filled up.

Answer 1

from my answer at Table-level backup

I am using bcp.exe to achieve table-level backups

to export:

bcp "select * from [MyDatabase].dbo.Customer " queryout "Customer.bcp" -N -S localhost -T -E

to import:

bcp [MyDatabase].dbo.Customer in "Customer.bcp" -N -S localhost -T -E -b 10000

as you can see, you can export based on any query, so you can even do incremental backups with this.

Answer 2

BCP is for dumping to / reading from a file. Use DTS/SSIS to copy from one DB to another.

Here are the BCP docs at MSDN