BCP is a utility that is installed by default with SQL Server editions and is used for bulk import or export of a large volume of data in user-defined formats. This utility is optimized for large data transfers either between SQL Server instances or between a SQL instance and a text file. The utility is used for both one-time data import/export and regular data loads within ETL processes. The utility is executed via the command-line (cmd) and has its own syntax.

BCP Syntax

You can easily view the utility’s parameters. Open the command prompt and type “bcp.” This will display a list of parameters that you can apply. Some of the most important bcp arguments include:

  • -S … server name (the server to which you are connecting)
  • -U … user name (the login under which you are connecting)
  • -P … password (the password for the login)
  • -d … database (the database to which you are connecting)
  • -n … native database data format

BCP Syntax

Data Export Syntax: Exporting data from a table to a file

  • [database_name.] schema.table_name | view_name | out data_file | format nul} – if exporting an entire table
  • query | queryout data_file | format nul} – if exporting based on a query

Data Import Syntax: Importing data into a table from a file

  • [database_name].schema.table_name | view_name | in data_file | format nul}

Example of Exporting/Importing SQL Data Using BCP – Bulk Copy Program

Let’s demonstrate how to export data from a table.

1) Preparing the Table – Create a simple table with 2 columns and populate it with data. The first column is a primary key, and the second column contains a GUID replicated 4 times. The second column has a text string of 148 characters on each row. In total, we generate 100,000 records.

Textove_pole VARCHAR(255)

DECLARE @Iterator INT=1



    INSERT INTO Temp.dbo.Test (Text_column)

SET @Iterator=@Iterator+1

IF @Iterator <= 100000 GOTO START_CYCLE

2) The destination directory for data export will be: C:\Destination_folder_bcp

3) The BCP command will look like this: [Temp].[dbo].[Test] out “C:\Cilovy_folder_bcp\Temp_dbo_Test_2018_07_02.bcp” -SSQL_INSTANCE -T -n

4) Result: Done. Alternatively, perform the import

BCP Result in CMD

For more information on the Microsoft documentation for the bulk copy program utility, click here.

5/5 - (1 vote)

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *