How To Use BCP Utility In SQL Server

BCP (bulk copy program) – a command line utility that bulk copies data between SQL Server database tables and data files. Using BCP, we can export data from a database table, view or a query to data files, generally text delimited files. Moreover, using BCP to import data into SQL Server database table from data text files. It is recommended to use BCP for loading a large number of data rows between SQL Server instances or data files.

BCP utility is available with SQL Server and usable through windows command prompt using BCP command. In case you do not have BCP utility, you may download it you may download it microsoft website.

Points to Ponder – Bulk Copy Program (BCP)

1.Recommended for performing export/ import for a large number of records.

2.Faster than traditional insert DML statement because it is minimally logged operation. SQL server avoids keeping large log files, skipping traditional checks of parsing, logging, optimization.

3.BCP doesn’t keep separate transaction log for each individual import/ export allowing to speed up the data loading operation.

BCP – Syntax & Important Options

BCP  DatabaseName.SchemaName.[TableName | ViewName] | Query
     [IN datafile | OUT datafile | queryout datafile | format]  
     [Options]

Argument and important options for BCP command.

Table Name – Fully qualified table name with a suffix of database name and schema name. This table will be used to import or export data. Refer as DatabaseName.SchemaName.TableName.

Query – SQL query to export data can be specified using this option instead of specifying the table name.

Direction – With the various option, we can specify import and export option as below.

OUT – Specifies that we are exporting data from a table to a file.
IN – Specifies that we are importing data from a file into a database table.
QUERYOUT – Copies data using SQL query, this will export data from a table to file.
FORMAT – Creates a format file based on options. Using this you can save time avoiding specifying the format information.

Additionally, we can use different options with the BCP command. Running BCP on command prompt lists out the various available options as following.

Only adding important options which are used for most cases here. For more option, you may refer to BCP msdn documentation.

-S server name – Name of SQL server instance to connect. If no instance name is specified then BCP by default connects to the default instance.

-U username – Name of the SQL user for connecting to SQL server instance.

-P password – SQL server instance’s password. If not specified, SQL server may ask for passport in prompt. If you are manually running the BCP then it’s advisable to not specify the password and wait for the prompt. Nonetheless, in a few cases, you are required to specify the password.

-T trusted connection – This option specifies that we are using integrated security for connecting to a database instead of specifying -U username and -P password.

-t field terminator – Delimiter to separate data values in a row. We may export data to pipe, comma or tab delimited file by specifying the field terminator option.

-d database name – If the database name is not specified earlier then we can still pass the database name argument explicitly using this option.

-c character type – Not specifying this option yields different file format. Performs an operation using a character data type. Using this option doesn’t prompt for defining the behaviour for each field. You may try skipping this option in example 1.1. BCP will ask file storage type/ prefix-length/ terminator for each filed. With -c option, we add tab field separator and newline as row terminator.

If -c is not specified, BCP will ask to format each field as follows.

Example Table & Data

If you want to run following BCP commands under your test database then you can create following employee table. Change the file path accordingly.

CREATE TABLE dbo.Employee
(
Id        INT PRIMARY KEY,
Name    VARCHAR(255),
Salary    INT
);

INSERT INTO dbo.Employee Values (1, 'Vish',     1000);
INSERT INTO dbo.Employee Values (2, 'Shailesh', 5000);
INSERT INTO dbo.Employee Values (3, 'Vikrant',  7000);
INSERT INTO dbo.Employee Values (4, 'Chetan',   12000);

SELECT * FROM Pilot2019.dbo.Employee;

1.

Export Data From Table To Data File

To walkthrough with an example, consider we have a table Employee in our test database. We shall export the data using OUT direction. A text file will be created on our hard drive with the data of the employee table. If the filename already exists, it shall be overwritten. Otherwise, a new file will be created.

BCP Pilot2019.dbo.Employee OUT "E:\SQLServerFiles\EmployeeExport.txt" -T -S DESKTOP-23ATANB\SQLSERVER17 -t, -c

Above command generates EmployeeExport.txt file with comma delimiter.

2.

Export Data From Table To Pipe Delimited Data File

In the following example, using database name, username and password to export Employee table to EmployeeExport.txt file. Remember that this will overwrite our comma separated file generated in the previous example.

BCP dbo.Employee OUT "E:\SQLServerFiles\EmployeeExport.txt" -S DESKTOP-23ATANB\SQLSERVER17 -U TestUser -P Test@123 -d Pilot2019 -c -t"|"

Understanding above command, notice that we have specified username -U, password -P and database name -d. Delimiter specified as pipe with -t option.

3.

Query To Export Data From Table To Data File

BCP includes an option to specify a SQL query to select needed columns from a table. This excludes specifying a table name instead. We may include a join query to fetch columns from multiple tables. By specifying QUERYOUT, we can input the SQL query with BCP.

In the following example, we have included a query to select only Name, Salary column from Employee table using QUERYOUT option.

BCP "SELECT Name, Salary FROM Pilot2019.dbo.Employee;" QUERYOUT "E:\SQLServerFiles\EmployeeExport.txt" -T -S DESKTOP-23ATANB\SQLSERVER17 -t, -c

4.

Import Data From File To Table

So far you have seen examples to export data from a table to file. In the following example, we are importing data from a file to the table. We need to specify direction as IN to import. Importing the EmployeeExport file, therefore, we shall truncate the employee first before importing.

Following command imports pipe delimited file into Employee table.

BCP Pilot2019.dbo.Employee IN "E:\SQLServerFiles\EmployeeExport.txt" -T -S DESKTOP-23ATANB\SQLSERVER17 -t"|" -c

Summary

In this tech-recipes post we have learned to use use BCP command in SQL Server. Using BCP we can export and import data between database and files. If you like this article you may walk through Tech-Recipes data archive posts to learn more useful stuff.