SQL Server Bulk Copy Program (BCP)

The bulk copy program (bcp) is a command-line utility that moves large volumes of data between SQL Server and a flat file. You can use it to:

• Import data from a file into a table (in)
• Export data from a table or view to a file (out)
• Export results from a query (queryout)
• Generate a reusable format file (format)

If you already understand BULK INSERT, think of bcp as the command-line equivalent that also supports exporting data (which BULK INSERT does not).

1. Install the command-line utilities. Ensure bcp is available on the machine that will run the command (workstation, jump box, VM, build agent). Verify:

   bcp -v

2. Decide where the file lives. For performance and simplicity, place import/export files on a fast local disk on the machine running bcp (or on a mounted share with appropriate throughput and permissions).
3. Use secure authentication. Prefer integrated security when possible:
   • -T for Windows integrated authentication (common on domain-joined systems)
   • -G for Microsoft Entra authentication when appropriate (especially for Azure-hosted services)
   Avoid embedding passwords in scripts unless you have a secure secret store and a controlled runtime environment.
4. Confirm permissions. Import requires table permissions such as INSERT. Export requires SELECT. If you are loading into staging tables, ensure the login has access to the target schema.
5. Plan the data format up front. For anything beyond a quick test, create and version a format file so imports remain stable as scripts evolve.

At a high level:

bcp [database_name.]schema.{table_name | view_name | "query"}
  { in data_file | out data_file | queryout data_file | format nul }
  [options]

The in/out/queryout/format keyword tells bcp what direction the copy is going. The remaining switches control formatting, authentication, batching, and performance.

Goal: load a CSV into Sales.dbo.Clients.

bcp Sales.dbo.Clients in "C:\data\clients.csv" ^
  -S localhost ^
  -T ^
  -c ^
  -t, ^
  -r "\n" ^
  -b 50000 ^
  -m 10 ^
  -e "C:\data\clients_import_errors.log" ^
  -o "C:\data\clients_import_output.log"

• -c uses character mode (typical for CSV).
• -t, sets comma as the field terminator.
• -r "\n" sets newline as row terminator (adjust if your file is CRLF).
• -b commits every N rows (reduces log pressure and improves restart behavior).
• -e captures rejected rows (critical for troubleshooting).
• -o captures command output for audit/diagnostics.

Best practice: import into a staging table first, validate row counts and data quality, then move into the final table with set-based SQL in a controlled transaction.

Goal: export a table to CSV.

bcp Sales.dbo.Clients out "C:\data\clients_export.csv" ^
  -S localhost ^
  -T ^
  -c ^
  -t, ^
  -r "\n"

If the output file exists, bcp overwrites it. For repeatable exports, standardize a directory layout and a naming convention (timestamped files, environment tags, etc.).

queryout lets you export a filtered dataset or a joined result set. The query must be quoted. If the query returns multiple result sets, only the first one is exported.

bcp "SELECT ClientID, Company, City, State, Zip
     FROM Sales.dbo.Clients
     WHERE State IN ('AZ','CA')
     ORDER BY Company" queryout "C:\data\clients_AZ_CA.csv" ^
  -S localhost ^
  -T ^
  -c ^
  -t, ^
  -r "\n"

Best practice: keep the query in source control (or generate it from a stored view) so exports remain consistent across environments.

Format files are the key to reliable, repeatable bulk operations. A bcp data file contains data, not schema. A stored format file documents the column order, types, and delimiters used for that table.

Create a format file

Create a non-XML format file:

bcp Sales.dbo.Clients format nul ^
  -S localhost ^
  -T ^
  -c ^
  -f "C:\data\Clients.fmt"

Create an XML format file (Windows utility supports -x):

bcp Sales.dbo.Clients format nul ^
  -S localhost ^
  -T ^
  -c ^
  -x ^
  -f "C:\data\Clients.xml"

Use the format file during import

bcp Sales.dbo.Clients in "C:\data\clients.csv" ^
  -S localhost ^
  -T ^
  -f "C:\data\Clients.fmt" ^
  -e "C:\data\clients_import_errors.log"

Best practice: store format files beside the import/export job definition (and treat them like code). If the table changes, update the format file deliberately rather than “fixing” the job ad hoc.

• Batching: use -b to reduce the blast radius of failures and manage log growth.
• Error capture: always use -e for imports so you can inspect rejected rows.
• Load hints: use -h hints (for example TABLOCK) when appropriate for large loads, especially into empty staging tables where locking behavior is acceptable.
• Separate concerns: land data first, validate second, transform/merge third.
• Operational logging: capture output via -o and keep a job run history.

• Avoid placing secrets directly in scripts. Prefer -T or -G and managed identity patterns where feasible.
• Limit inbound SQL exposure; use private networking, jump hosts, or bastion access rather than public endpoints.
• If exporting sensitive data, treat the data file as sensitive (encryption at rest, access controls, retention policies).

bcp Sales.dbo.Employee in "C:\data\employee.tsv" ^
  -S WesternRegion ^
  -T ^
  -c ^
  -t "\t" ^
  -r "\n"

1. This example loads rows from employee.tsv into Sales.dbo.Employee.
2. It connects to the WesternRegion server using integrated security.
3. Data is character format with tab-delimited fields and newline-delimited rows.

In the next lesson, you will be introduced to importing data using built-in tooling and common workflows for moving data between systems.

Note on legacy syntax: Older Transact-SQL extensions such as COMPUTE BY are not a modern approach for producing multiple result sets. For summaries, prefer standard SQL patterns such as GROUP BY with ROLLUP or separate aggregation queries, then export a single, well-defined result set.