SQL Server Bulk Copy Program (BCP)

The bulk copy program (bcp) is a command-line utility that moves 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, covered earlier in this module, think of bcp as its command-line counterpart with export added: BULK INSERT only moves data into SQL Server, bcp moves it in either direction, and it runs from outside the database entirely rather than from within a T-SQL session.

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

   bcp -v

2. Decide where the file lives. For performance and simplicity, place import and 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, supported directly by SQL Server 2022 and later, as well as by Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.
   Avoid embedding passwords directly 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, covered later in this lesson, so imports remain stable as scripts evolve.

Unlike the T-SQL statements this module has covered so far, bcp isn't something you run inside a query window at all; it's a separate executable invoked from a command line. On Windows, that's Command Prompt or PowerShell. On Linux or macOS, the SQL Server command-line tools ship as the mssql-tools18 package, which installs bcp alongside sqlcmd and depends on the Microsoft ODBC driver for SQL Server. Older material pointing at Azure Data Studio for this kind of work is out of date, since Azure Data Studio was retired earlier this year, as this course has already noted; bcp itself was never an Azure Data Studio feature anyway, it's always been a standalone command-line tool, run the same way whether the surrounding workflow happens to use SSMS, the VS Code MSSQL extension, or nothing at all.

Because it's a separate executable, bcp also drops neatly into places a T-SQL statement can't reach on its own: a scheduled Windows Task, a step in a SQL Server Agent job, or a stage in a CI/CD pipeline running on a build agent. That's part of why bcp and BULK INSERT aren't simply redundant with each other, covered next.

The two tools solve overlapping problems in different ways, and the difference matters for more than just syntax. BULK INSERT runs inside SQL Server itself, as a T-SQL statement, which means the SQL Server service account has to be able to read the file directly: a local path, a UNC share, or Azure Blob Storage through an external data source, all covered in the previous lesson. bcp runs outside SQL Server and streams the data over the connection, so the file only has to be readable by whatever account is running bcp itself, on whatever machine that happens to be. A file sitting on an analyst's own laptop, or produced by a build agent that has no file-system access to the database server at all, is exactly the case where bcp is the only option between the two.

That same difference cuts the other way on performance. When the file is already sitting somewhere the SQL Server service account can reach directly, BULK INSERT usually has the edge, since the data never has to leave the server or cross the network as part of the load. bcp has to push every row from wherever it's running to the server, which adds network overhead that scales with the size of the file.

Exporting is the other deciding factor, and it isn't really a choice at all: BULK INSERT only loads data into SQL Server, so any task that needs data to come out, a scheduled export to a partner system, a nightly extract for a reporting tool, a one-off queryout for an ad hoc request, has to use bcp. A team that already has both tools in its toolbox typically ends up using BULK INSERT for straightforward loads a T-SQL script can trigger on its own, and bcp for exports, for loads driven from outside the database, and for anything that needs to run from a machine that isn't the SQL Server itself.

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 connection, data representation, batching, and performance.

bcp {dbtable | query} {in | out | queryout | format} datafile
    [-S server_name] [-d database] [-T] [-G] [-U username] [-P password]
    [-c] [-w] [-n] [-N]
    [-t field terminator] [-r row terminator]
    [-F firstrow] [-L lastrow] [-b batchsize] [-m maxerrors]
    [-e errfile] [-o outfile]
    [-f formatfile] [-x]
    [-k] [-E] [-R] [-h "load hints"] [-v]

Three switches are worth calling out specifically, because they're the exact command-line equivalents of options this module already covered for BULK INSERT. -k keeps empty fields as NULL instead of letting a column's default constraint fill them in, matching BULK INSERT's KEEPNULLS. -E loads the identity values already present in the file instead of letting SQL Server assign new ones, matching KEEPIDENTITY, and requires ALTER TABLE permission on the target. -R tells bcp to interpret currency, date, and time values using the client computer's regional settings; by default, bcp ignores those settings entirely.

-h passes import hints in quotes, and the available hints, ORDER, ROWS_PER_BATCH, KILOBYTES_PER_BATCH, TABLOCK, CHECK_CONSTRAINTS, and FIRE_TRIGGERS, are the same set BULK INSERT's WITH clause exposes directly as named options, just passed through a single switch here instead. One genuine difference between the two tools is worth remembering: in BULK INSERT, BATCHSIZE and ROWS_PER_BATCH can both be specified at once, with BATCHSIZE governing the actual commit behavior. In bcp, -b and the ROWS_PER_BATCH hint are mutually exclusive, so only one or the other can be used for a given copy, not both.

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 a CSV file.
• -t, sets a comma as the field terminator.
• -r "\n" sets newline as the row terminator; adjust this if the file uses CRLF line endings instead.
• -b commits every 50,000 rows, reducing log pressure and improving restart behavior if the load is interrupted partway through.
• -e captures rejected rows, critical for troubleshooting a load that comes up short.
• -o captures command output for an audit trail.

Best practice: import into a staging table first, validate row counts and data quality, then move into the final table with set-based SQL inside a controlled transaction, the same load-then-validate pattern this module has already applied to INSERT, SELECT INTO, and BULK INSERT.

The same pattern works against this course's own Inventory table. Loading a nightly price-update file is a one-line change from the example above:

bcp dbo.Inventory in "C:\data\inventory_update.csv" ^
  -S localhost ^
  -T ^
  -c ^
  -t, ^
  -r "\n" ^
  -b 10000 ^
  -m 5 ^
  -e "C:\data\inventory_update_errors.log"

The same five columns, StockNum, Cost, RetailPrice, QtyAvailable, and LastPriceChangeDate, that BULK INSERT loaded directly from T-SQL in the previous lesson load here too, just invoked from outside the database instead of from inside a query window.

Because bcp runs outside the database, it doesn't give you a result set back the way an INSERT or a SELECT INTO does; the console output tells you how many rows copied and how long it took, but confirming the data actually landed correctly still means going back into SSMS or the VS Code MSSQL extension afterward:

SELECT COUNT(*) AS rows_loaded
FROM dbo.Inventory;

SELECT TOP (25) *
FROM dbo.Inventory
ORDER BY LastPriceChangeDate DESC;

That same row-count-and-sample check has shown up after every loading method this module has covered; bcp doesn't change the need for it, it just moves the loading step one layer further from the database itself.

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 already exists, bcp overwrites it. For repeatable exports, standardize a directory layout and a naming convention: timestamped file names and environment tags both make it obvious, at a glance, which export is which.

queryout lets you export a filtered dataset or a joined result set rather than an entire table or view. The query must be quoted, and if it happens to return more than one result set, only the first one is written to the file; any additional result sets are silently ignored.

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 itself in source control, or generate it from a stored view, so exports stay consistent across environments instead of drifting as someone tweaks a one-off command in place.

Format files are the key to reliable, repeatable bulk operations. A bcp data file contains data, not schema; a stored format file is what documents the column order, data types, and delimiters that belong with that file.

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 instead, Windows only:

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

The resulting non-XML file follows the same structure this module already walked through when it introduced format files for BULK INSERT: a version line, a field-count line, then one row per field giving the field's position in the source file, its data type, a prefix length, its maximum length, its terminator, the destination column's position, the column's name, and its collation. A format file generated against Inventory would look exactly like the one built in that earlier lesson; bcp and BULK INSERT read the same file format, so a format file written for one works for the other.

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 or export job definition and treat them like code. If the table changes, update the format file deliberately rather than patching the job ad hoc the next time something breaks.

• Batching: use -b to reduce the blast radius of a failure and manage transaction log growth on a large load.
• Network packet size: -a sets the network packet size in bytes, from 4096 up to 65535, with 4096 as the default. A larger packet size can help throughput on a fast, reliable network link moving a large file.
• Error capture: always use -e for imports so rejected rows can actually be inspected afterward, rather than simply vanishing.
• 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 and merge third.
• Operational logging: capture output with -o and keep a job run history so a failed load from last week is still explainable today.

• 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 itself as sensitive: encryption at rest, access controls, and a defined retention policy all apply to it just as they would to the database it came from.

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, importing and exporting data moves to SQL Server's built-in tooling and wizard-driven workflows, rounding out the command-line approach this lesson covered with the GUI side of the same task.