DB-유

Using bcp for Fast SQL Server Data Export and Import

· mssql, tools, data

The Bulk Copy Program, bcp, has shipped with SQL Server since the Sybase days, and it is still the fastest way to get a large table out to a file or back in without writing code. It streams rows through the bulk-load API, bypasses most of the per-row overhead of INSERT, and can be minimally logged. I use it for copying a table between instances that cannot see each other, for seeding test environments, and for unloading archive data before a purge.

Below are export, import, query-based export, format files, and the switches that separate a one-hour load from a five-minute one. Examples are from SQL Server 2017; bcp also comes in the mssql-tools package on Linux with the same syntax. (bcp -v tells you if it's installed. You'll need SELECT on the source, and INSERT plus ADMINISTER BULK OPERATIONS for imports.)

Native format export

bcp ShopDB.dbo.Orders out D:\bcp\Orders.dat -n -S sqlsrv01 -T -e D:\bcp\Orders_err.txt
  • -n writes native (binary) format. It is the fastest and preserves types exactly, but is only readable by SQL Server.
  • -S is the server, -T uses a trusted (Windows) connection. Use -U user -P password for SQL authentication.
  • -e writes any errors to a file instead of losing them in the console.

For SQL Server to SQL Server transfers, always use -n (or -N if the table contains Unicode columns to avoid conversion).

Character format for other systems

bcp ShopDB.dbo.Customers out D:\bcp\Customers.csv -c -t "," -r "\n" -C 65001 -S sqlsrv01 -T
  • -c writes character format; every column becomes text.
  • -t "," sets the field terminator; the default is a tab. -r "\n" sets the row terminator.
  • -C 65001 writes UTF-8, which is what most other tools expect.

Be aware that -c does not quote fields, so a comma or newline inside a value corrupts the file. For messy string data, either use tab as the delimiter or export with a queryout that wraps values in quotes.

Exporting a query

bcp "SELECT OrderId, CustomerId, TotalAmount FROM ShopDB.dbo.Orders WHERE OrderDate >= '2018-01-01' ORDER BY OrderId" queryout D:\bcp\Orders2018.dat -n -S sqlsrv01 -T

queryout is how you export a subset or join. The query must be fully qualified because bcp connects to the login's default database.

Importing

Create the target table with the same column order first, then:

bcp ShopDB.dbo.Orders_Archive in D:\bcp\Orders.dat -n -S sqlsrv02 -T -b 50000 -h "TABLOCK" -e D:\bcp\import_err.txt
  • -b 50000 commits every 50,000 rows instead of loading the whole file in one transaction. This bounds the transaction log growth and lets you resume after a failure.
  • -h "TABLOCK" takes a table lock, which is required for minimal logging (with the database in SIMPLE or BULK_LOGGED recovery and no existing rows or clustered index data being maintained per-row).
  • Add -E if the target has an identity column and you want to keep the values from the file. Without -E, identities are regenerated.
  • By default, constraints and triggers are not checked on import. Add -h "CHECK_CONSTRAINTS,FIRE_TRIGGERS" if you need them, at a performance cost.

I load into a heap with no non-clustered indexes and create the indexes afterwards. It's nearly always faster than loading into an indexed table.

Format files, when columns don't line up

When the file has different column order, missing columns or different terminators, generate an XML format file from the target table and edit it:

bcp ShopDB.dbo.Customers format nul -c -x -f D:\bcp\Customers.xml -t "," -S sqlsrv01 -T

Then import with -f:

bcp ShopDB.dbo.Customers in D:\bcp\Customers.csv -f D:\bcp\Customers.xml -S sqlsrv01 -T -F 2

-F 2 skips a header row by starting at row 2. The same format file also works with BULK INSERT and OPENROWSET(BULK ...) in T-SQL.

Did every row arrive?

Compare row counts and a checksum:

SELECT COUNT(*), CHECKSUM_AGG(CHECKSUM(*)) FROM ShopDB.dbo.Orders;          -- source
SELECT COUNT(*), CHECKSUM_AGG(CHECKSUM(*)) FROM ShopDB.dbo.Orders_Archive;  -- target

Also check that the -e error file is empty. I skipped that once and lost rows without noticing. bcp continues past bad rows by default (up to -m errors, default 10), so a "successful" run may have skipped rows.

Errors you'll probably meet

"SQLState = S1000, NativeError = 0. Error = [Microsoft][ODBC Driver 17 for SQL Server]Unexpected EOF encountered in BCP data-file." The file's format does not match the switches: usually a -c file imported with -n, the wrong terminators, or a UTF-8 BOM at the start. Recheck -c/-n/-t/-r and use -F 2 for headers.

"Invalid character value for cast specification" A string in the file cannot convert to the target column type, typically a date in a regional format. Use ISO 8601 dates in exports or set the column to varchar in a staging table and convert in T-SQL.

Import is slow and the log grows. The database is in FULL recovery, or you did not use TABLOCK, or the table has a clustered index and the file is not sorted by it. Use -h "TABLOCK,ORDER(OrderId ASC)" when the file is sorted on the clustered key.

"Copy direction must be either 'in', 'out' or 'format'." A shell quoting problem; in PowerShell, wrap the query in double quotes and avoid $ characters, or call bcp from cmd.exe.

bcp isn't glamorous, but it's fast and it's everywhere SQL Server is.