CODEWITHSUNDEEP
sql-server-2008bcp
GShenanigan
GShenanigan

Reputation: 5493

Can BCP copy data directly from table to table?

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.

Upvotes: 4

Views: 19555

Answers (4)

userSteve
userSteve

Reputation: 1644

TableDiff.exe might do what you want

https://solutioncenter.apexsql.com/automatically-compare-and-synchronize-sql-server-data/

Upvotes: 0

Gonzalo
Gonzalo

Reputation: 31

SQL Import/Export wizard will do the job ... just connect twice to same database (source and destination) and copy one table onto other (empty and indexed), you might want to instruct to ignore autonumeric Id key field if exists. This approach works for me with tables over 1M+ records.

Upvotes: 2

kenwarner
kenwarner

Reputation: 29120

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.

Upvotes: 8

rickythefox
rickythefox

Reputation: 6851

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

Upvotes: 2

Related Questions