Fast Exports With bcp

A large table does not need a spreadsheet window to leave SQL Server. Fast exports with bcp stream rows to a file with little ceremony. Pick the right file format first, because a fast export that nobody can read is still a problem.

Start With the Receiving System

Before typing a command, ask who will read the file. Another SQL Server instance can use native format, which preserves SQL Server data representations and avoids character conversion. A partner application usually needs character or Unicode character data. Ask for its delimiter, encoding, null convention, column order, and date representation. Do not assume that a file called CSV is safe merely because commas appear in it. bcp character mode is delimiter based. It does not supply all CSV quoting rules for you.

I start by writing a tiny export and having the receiving system read it. That check catches an expensive mistake before the large table moves. How will the consumer distinguish a delimiter inside a value from a delimiter between fields? If the answer is a shrug, the format agreement needs work. The file extension can wait.

Use a Query for Narrow Fast Exports With bcp

The queryout option exports the result of a SELECT. It lets you choose columns and predicates instead of dumping every field in a table. The command below writes database names and IDs from a local default instance to a character file in the current directory. Run it in Command Prompt where bcp is installed and you have access to the instance. Replace the instance and query with your approved source after the small test works.

I prefer an explicit column list because a schema change should not silently change the file. Add ORDER BY only when the receiver needs an order. Sorting a large export has a cost. Record the query beside the file so you can reproduce the extraction. The data file alone cannot tell you which WHERE clause produced it.

REM Command line
bcp "SELECT name, database_id FROM sys.databases ORDER BY name" queryout databases.txt -S . -d master -T -c

Choose Native Format Deliberately

Native mode uses -n. It is a good fit when the destination is SQL Server and the source and destination agree on the schema and types. It is not a portable text contract. A text editor showing odd characters in a native file has found exactly what it should find. Native files need a matching import plan, and version or type differences still deserve a test on the target instance.

The second command uses the same source query, this time in native mode. Keep its output separate from the character file. Verify the destination can import a sample before exporting the whole table. If non-ASCII text must travel between SQL Server instances, also review Unicode native format and its type behavior. A format choice should follow the data, not the command you remember from last year.

REM Command line
bcp "SELECT name, database_id FROM sys.databases ORDER BY name" queryout databases.native -S . -d master -T -n

Know What a Format File Does

A format file records field mapping and data representation for bcp. It becomes useful when an incoming file does not match the destination table’s column layout, or when you want a repeatable definition instead of answering interactive prompts. Generate one from a representative table or view using bcp format, then inspect it. Store the format file beside the transfer specification and test it with the target operation.

A format file is not a substitute for agreeing on semantics. It cannot decide whether a blank field means NULL, whether a timestamp is local time, or whether embedded line breaks are permitted. I have seen teams spend longer debating a delimiter than generating the export. That debate was time well spent. Use a small data sample containing nulls, long strings, separators, and non-ASCII text to test the layout.

REM Command line
bcp master.sys.databases format nul -f databases.fmt -S . -T -n

Separate Fast Exports With bcp From Import Batches

The -b option sets rows per batch for a bulk import. A batch is a transaction boundary on the receiving side. It does not turn one export into a series of faster export transactions. That distinction matters when someone suggests adding -b to every bcp command as a performance charm. For fast exports with bcp, the important questions are source scan cost, network path, disk throughput, and whether the selected query sorts or transforms data.

For an import, batch size changes rollback scope and log behavior. Choose it through a target-side test with representative data. Keep error handling and restart plans in the same discussion. A partial load after an interrupted import is operationally different from a completed export file. Record which rows or batches reached the target before retrying.

Keep the Source Query Predictable

A long export reads data while the application can still change it. Decide whether the file needs a transactionally consistent snapshot, a business cutoff, or just a current best effort view. Snapshot isolation can help where it is enabled and sized for the workload. A stable key range can support repeatable chunks, but each chunk needs a boundary recorded in a manifest. Do not claim a consistent export because every file has a timestamp in its name.

Check the execution plan for filters and sorts before launching a production-size run. Fast exports with bcp are not free merely because the tool runs outside SSMS. It is still a query against the server. I watch the source workload during an initial run and make the extraction window part of the schedule.

Validate More Than the Exit Message

Record the bcp exit status, file size, source query, extraction time window, and the expected destination. Compare a source count taken under the same filter with the imported count when the workflow requires exact coverage. Counts alone do not detect corrupted characters or shifted fields. Sample boundary values, nulls, and long text at the receiver. Keep a checksum or file hash for transfer integrity, then validate the data after import.

When a file appears smaller than expected, investigate format and filtering before declaring victory. Native and character output have different byte sizes. Compression changes them again. The only useful success condition is that the target can read the intended rows with the intended values.

Make Fast Exports With bcp Repeatable

Save the command, format file, schema version, target expectations, and validation steps together. Use an approved folder with enough free space and a predictable cleanup policy. Do not write a sensitive export into a casual working directory merely because the command is shorter. Restrict access to the file and remove it under your retention rules after the transfer has been verified.

Run a small rehearsal after driver, server, or schema changes. The command that worked yesterday can still produce a file whose meaning changed today. A repeatable export is one you can explain, rerun, and validate. Speed follows from that discipline because you avoid the second transfer nobody planned for.

Related reading on this blog: Simple Example of BCP Command Line Utility and Export Data AS CSV from Database Using SQLCMD.

A fast export is not a finished transfer, it is a file the receiver can prove it read correctly.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.