Can BCP copy data directly from table to table?

sql-server-2008 bcp
GShenanigan picture GShenanigan · Nov 3, 2010 · Viewed 15k times · Source

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

kenwarner picture kenwarner · Jan 5, 2013

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.

Related questions

Use bcp to import csv file to sql 2005 or 2008

I have a csv file and i need to import it to a table in sql 2005 or 2008. The column names and count in the csv are different from the table column names and count. The csv is splitted by a …

Read More
bcp: Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation

I have recently encountered an error while working with bcp. Here is the error. SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation I'm trying to unpack the data into a staging table which does not have …

Read More
Can I specify an input sql file with bcp?

How can I specify an input sql file with a long query when using bcp? I tried using the -i option but it keeps complaining about a command-line error with no extra information. Is this possible?

Read More