How to Copy Large Volumes of Data from One Table to Another Database[Or 18Gb in 5min]

Today’s SQL challenge: Copying an 18.2 GB table (60 million rows) from one SQL Server to several others.

When reviewing data with T-SQL for Machine Learning exploration, you discover that one of the fields is a VARBINARY(max). This prevents the use of backup/restore compression and slows down data transmission. During testing, table creation alone took over 11 minutes.

Another option is using SSIS with a Balanced Data Distributor, but it requires transferring 18.2 GB across multiple networks, creating heavy network load.

Preferred approach:

• Use BCP in parallel to export data into multiple files.
• Compress each file in parallel.
• Transfer compressed files to destination servers.
• On each destination, use BCP in parallel to import into separate tables.
• If consolidation is needed, use partitioned tables or views.

My Method

PowerShell commands used for extraction and compression:

Start-Process PowerShell -ArgumentList {
    New-Item -ItemType Directory -Path D:\Backup\Backup1\;
    
    bcp "SELECT * FROM Database.dbo.Table WHERE Id BETWEEN 1 AND 3750000" `
    queryout D:\Backup\Backup1\Backup1.out -S. -T -N -a 65535;
    
    Add-Type -AssemblyName System.IO.Compression.FileSystem;
    
    [IO.Compression.ZipFile]::CreateFromDirectory('D:\Backup\Backup1', 'D:\Backup\Backup1.zip');
}

Running these commands in parallel lets you launch multiple tasks simultaneously, completing the entire process in about 4 minutes.

In this setup, the SQL Server instance has 20 cores, with MAXDOP set to 8.

No noticeable performance difference was observed between using 8 vs. 20 threads — total runtime remained nearly identical.

However, running a single thread can backfire — while SQL throughput spiked to 265K rows/sec, the zipping phase slowed to 6 minutes, pushing total time to 11 minutes.

After compression, all ZIP files totaled just 750 MB. On a 100 GbE network, transfer to each target server is nearly instant. Offline transfer is also an option.

Once the data is in place, unzip in parallel — it takes just a few seconds. Even serially, it finished in 99 seconds.

Next, load data into individual tables using PowerShell and BCP (example, single line):

Start-Process PowerShell -ArgumentList {
    bcp Database.dbo.Backup1 in D:\Backup\Backup1.out `
    -S. -T -N -a 65535 -h "TABLOCK" -b 10000
}

All loads completed in 61 seconds. Attempting this directly into a single table causes contention and takes about 30 minutes instead.

Total end-to-end time: 5.3 minutes.

Final note: you’ll have multiple tables. To consolidate, you can create a VIEW or copy the rows. But the fastest method is using partition functions and schemes to logically merge them - takes virtually no time.

Ideas for additional speed improvements:

• If this operation will be repeated, consider partitioning the source table. A well-designed partitioning scheme lets you transfer only new or changed data, not the entire table. Partitions can be moved to separate tables before export, improving performance.
• Use multiple drives for output files if possible. I had only one spare drive besides the DB drive, but using locally mounted SSDs boosts throughput.
• Enable jumbo network packets to increase data transfer rates between storage and the DB server.
• If the table is in-memory, data doesn’t need to travel through storage or the network, which speeds things up.
• Pre-grow the destination database before loading — this reduces delays during import.
• More tips available in Microsoft’s article.