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.
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