Export and Import Data with The BCP Utility

Problem

I needed to do a one time data migration from one MSSQL Database Server to a different server. This table was very large with 1.5+ million rows. I only needed to migrate the rows that were newer than 2026-06-09.

Attempts

The SSMS export data wizard exported the data, but didn’t have a query filter option. It created a 9gb file, I couldn’t open it to change it as I only needed newer than 2026-06-09.

Solution

I installed the bcp utility: Bulk Copy with bcp Utility - SQL Server and opened up a Terminal.

Export the data

bcp "select * FROM MySourceLoadTable WHERE Date_Loaded > '2026-06-09 07:45' ORDER BY Date_Loaded DESC" queryout "C:\git\Load\data.bcp" -c -T -S MyDbServer

This exported the data into a .bcp binary file.

Get the format

bcp MySourceLoadTable format nul -S MyDbServer -T -f C:\git\Load\data.fmt -c

This created the fmt table describing the columns. Without the fmt file, the bcp will prompt for each columns definition.

Import the data into the target database

bcp MyDestinationLoadTable in C:\git\Load\data.bcp -S MyTargetDbServer -T -f C:\git\Load\data.fmt

It took a few seconds

Sample output:

Starting Copy...
1000 rows sent to SQL Server. Total sent: 33000
(rows left out for brevity)

33326 rows copied.
Network packet size (bytes): 4096

Related

I wrote a Sql Bulk Copy article way back in 2021 .