All geek questions in one place

Can BCP copy data directly from table to table?

I have a situation where I need to copy several tables from one SQL Server database to a separate SQL Server database. Databases are in one instance. The tables that I copy contain at least 4.5 million rows and are about 40 GB in size.

I used BCP before, but I am not very familiar with it and could not find the documentation on whether it is possible to use BCP to copy directly from a table to a table without writing to a file between them.

Is it possible? If so, how?

EDIT: The reason we don’t use simple INSERT is because we have limited disk space on the log of the server, which almost instantly disappears when you try INSERT. We really tried to do this, but the request quickly slowed down when the log stream was full.

+3
source share
3 answers

BCP is designed to flush / read from a file. Use DTS / SSIS to copy from one database to another.

Here are the BCP docs on MSDN

+2
source

from my answer to table level backup

I use bcp.exe to create table level backups

for export:

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

for import:

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

, , .

+6

The SQL Import / Export Wizard will complete the task ... just connect to the same database twice (source and destination) and copy one table to another (empty and indexed), you can ask to ignore the auto-ID field if it exists. This approach works for me with tables over 1M + entries.

+2
source

Source: https://habr.com/ru/post/1772887/

More articles:


All Articles