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.
|
1 2 3 |
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:
|
1 |
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:
|
1 |
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:
|
1 |
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 |
The -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 viaORDER(OrangeID ASC). Use this option if the table has a clustered index. TheORDERclause:
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 theCHECK_CONSTRAINTShint,CHECKandFOREIGN KEYconstraints are ignored, and after the operation those constraints on the table are marked “not-trusted”.
The -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.
Hello, could you tell me if this is possible. We get regular dacpac files from external source, in which we need to extract one column from one table every day. I can of course use BCP to fill a DB on SQL server and then extract it to finish the script. There is non sql server on the machine and we’d like to keep it on that machine without installing it. Is there a command I coud use with BCP (or other tool) to directly extract needed data from de dacpac file (or BCP files inside it). Thanks.
Have you tried unzipping the dacpac via 7Zip or similar utility? A dacpac is essentially just a zip archive with specific files necessary for sqlpackage.exe. Once you do that, you may be able to use bcp to import the data you need into a #temp table as a staging step. From there you’d run some T-SQL code to import the desired column.
Hello Hanna and thanks for your response. I am actually looking for a solution that would not require the use of an instance of SQL server. To my knowledge, importing into a #temp table does require it unfortunately.
My suggestion of staging into a #temp table was an assumption that you’d be using SQL Server at some point in the process. Having said that, it might be advantageous to use the free SQL Server Express Edition to extract the dacpac. Note that you don’t need Microsoft Windows to run SQL Server, in case that is a concern. If I get a chance today, I’ll look into other options, as well.
You may want to ask the question on https://dba.stackexchange.com too
This is exactly my plan now Hannah. No need to go in the trouble of finding an SQL instance free solution.