Example BCP export and import commands
The Microsoft Bulk Copy Utility, BCP.exe, can be used to copy data from a table in one SQL Server instance to the same table in another SQL Server instance. Since the BCP Utility is designed to cover a vast array of possible requirements, the command-line switches can be daunting for new users, or folks who don’t often use it. This post shows several example BCP commands to copy data from a table in one database, to the same table in another database or SQL Server instance.
The following code executes the BCP utility three times. The first command extracts data from the table
"dbo.tablename" into the filesystem file specified in the
"outputfile" parameter, from the SQL Server instance specified in
"SQLServerName", and the database specified in
"databasename". The second command creates a BCP format file which captures relevant aspects of the DDL definition of the table. The third command imports the data into the target table, database, and SQL Server instance.
bcp "dbo.tablename" out "outputfile" -S "SQLServerName" -T -d "databasename" -n -q
bcp "dbo.tablename" format nul -S "SQLServerName" -T -d "databasename" -n -f "formatfilename" -x
bcp "dbo.tablename" in "inputfilename" -S "SQLServerName" -T -h "TABLOCK, ORDER([ColumnName] ASC), CHECK_CONSTRAINTS" -b rowsperbatch -f "formatfilename" -E -d "databasename" -q
Since a real-world-example often helps understand those commands more easily, consider the following example where I’m exporting data:
bcp "dbo.Oranges" out "C:\some\path\Oranges.bcp" -S "FRUIT\PEARS" -T -d "Fruit" -n -q
That creates a binary BCP file named
C:\some\path\Oranges.bcp that contains data from the
dbo.Oranges table, in the
Fruit database, which exists in the
FRUIT\PEARS SQL Server instance. The
-T parameter specifies to use a “Trusted Connection”, which typically means connect via the currently logged-in users’ Active Directory account.
Use this command to create the format file for that table:
bcp "dbo.Oranges" format nul -S "FRUIT\PEARS" -T -d "Fruit" -n -f "C:\some\path\Oranges.fmt" -x
Then, use this command to import the data from the bcp file into the
dbo.Oranges database on the target SQL Server:
bcp "dbo.Oranges" in "C:\some\path\Oranges.bcp" -S "FRUIT\PEACHES" -T -h "TABLOCK, ORDER(OrangeID ASC), CHECK_CONSTRAINTS" -b 1000 -f "C:\some\path\Oranges.fmt" -E -d "Fruit" -q
-h "TABLOCK, ORDER(OrangeID ASC), CHECK_CONSTRAINTS" parameter tells BCP to:
TABLOCKlocks the entire table for the duration of the bulk import process:
Specifies that a bulk update table-level lock is acquired for the duration of the bulk load operation; otherwise, a row-level lock is acquired. This hint significantly improves performance because holding a lock for the duration of the bulk-copy operation reduces lock contention on the table.
- Assume rows in the bcp source file, “C:\some\path\Oranges.bcp”, are ordered by
OrangeIDin ascending order via
ORDER(OrangeID ASC). Use this option if the table has a clustered index. The
Specifies the sort order of the data in the data file. Bulk import performance is improved if the data being imported is sorted according to the clustered index on the table, if any. If the data file is sorted in a different order, that is other than the order of a clustered index key, or if there is no clustered index on the table, the ORDER clause is ignored. The column names supplied must be valid column names in the destination table. By default, bcp assumes the data file is unordered. For optimized bulk import, SQL Server also validates that the imported data is sorted.
CHECK_CONSTRAINTSensures enforcement of constraints on the target table during bulk-import. Without the
FOREIGN KEYconstraints are ignored, and after the operation those constraints on the table are marked “not-trusted”.
-b 1000 option tells BCP to send rows to the destination SQL Server in batches of 1,000 rows per transaction. For target databases using the simple recovery model, this can reduce transaction log use by allowing SQL Server to truncate the log between batches. However, if a problem occurs during a batch, all previous batches will remain committed in the target table. From the BCP documentation:
Specifies the number of rows per batch of imported data. Each batch is imported and logged as a separate transaction that imports the whole batch before being committed. By default, all the rows in the data file are imported as one batch. To distribute the rows among multiple batches, specify a batch_size that is smaller than the number of rows in the data file. If the transaction for any batch fails, only insertions from the current batch are rolled back. Batches already imported by committed transactions are unaffected by a later failure.
Hopefully, this post provides a simple explanation of how to use the BCP utility to reliably import and export data from SQL Server.
Check out the rest of our posts in the Tools section.
If you found this post useful, please
consider donating a small amount
to help keep the lights on and site running.