The BCP (Bulk Copy Program) command in action

There are various methods available for bulk data operations.

BCP utility
BULK INSERT
Using OPENROWSET
Import/Export wizard

The BCP (Bulk Copy Program) utility is a command line that program that bulk-copies data between a SQL instance and a data file using a special format file. The BCP utility can be used to import large numbers of rows into SQL Server or export SQL Server data into files. The BCP data files don’t include any schema details or format information. Hence, it is recommended to create a format file to record the data format so in case of any failures, you can refer to the format file and better understand the data format to determine what may have gone wrong..

We’ve been using the BCP tool for a long time, the reason being that it has a very low overhead, and works great for bulk exporting and importing of data. It is one of the most efficient ways to handle bulk import and export of data.

Article overview

In this article, the BCP utility will be explained in detail. It covers the following topics:

BCP import and export command
The format file
How to create format file
How to use the format file for data management
and more…

Configuration

SQL Server supports exporting of data from a SQL Server table and importing the same into another table. To run the BCP command in ssms, enable the xp_cmdshell server configuration parameter. This gives you the control to run extended stored procedures. Keep in mind, though, that this is not always a recommended option, since it exposes the SQL Server surface to potential threats from the outside world.

1	EXEC master..xp_cmdshell 'BCP ProdSQLShackDemo.dbo.SQLShackAuthor OUT f:\PowerSQL\ProdSQLShackDemo.txt -T -c'

BCP IN | OUT | QUERYOUT options

OUT: This option is used to export (or dump) all the records from a table into a data file. For example,

1	C:\WINDOWS\System32>BCP [AdventureWorks2014].[dbo].[SalesOrderDetail] out C:\SODetail_Out.txt -S hqdbt01\SQL2017 -T -c –b1000 –t,

SQLShackDemoATC.dbo.SQLShackDemo – This is the name of the table that we intend to export. We can also use -d option to include the database name.
c :\SODetail_Out.txt – This is the output file where the data is dumped to
-T – Trusted Windows authentication
-t, – define comma as the field separator
-w – Use wide width data format
-b1000 – Export the data in batches of 1000 rows

IN: This option is used to import all the records to an existing table. This requires the table to be created before executing the BCP command.

Let’s create a table to import the data, called SalesOrderDetailsIn using the following create statement

USE [AdventureWorks2014]

CREATE TABLE [dbo].[SalesOrderDetailIn](

[SalesOrderID] [int] NOT NULL,

[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,

[CarrierTrackingNumber] [nvarchar](25) NULL,

[OrderQty] [smallint] NOT NULL,

[ProductID] [int] NOT NULL,

[SpecialOfferID] [int] NOT NULL,

[UnitPrice] [money] NOT NULL,

[UnitPriceDiscount] [money] NOT NULL,

[LineTotal] [numeric](38, 6) NOT NULL,

[rowguid] [uniqueidentifier] NOT NULL,

[ModifiedDate] [datetime] NOT NULL

) ON [PRIMARY]

We can see that in the following BCP command that the IN keyword is used to import the data from the SODetails_Output.txt file.

1	C:\WINDOWS\System32>BCP [adventureworks2014].dbo.[SalesOrderDetailIn] in C:\SODetail_Output.txt -S hqdbt01\SQL2017 -T –c

BCP QUERYOUT

In the following example, we’re not going to export an entire table. Let’s look at a way to run a SQL statement to generate a data dump. The BCP export command is used in conjunction with the SELECT statement and the queryout option. Using queryout the SQL statement can be run on a defined connection and the data can be dumped into another file; the other switches are the same in this example as well.

BCP "SELECT [SalesOrderID], [SalesOrderDetailID],[CarrierTrackingNumber] FROM [AdventureWorks2014].[dbo].[SalesOrderDetail]" queryout C:\SOQueryOut.txt -S hqdbt01\SQL2017 -T –c

CREATE TABLE [dbo].[SalesOrderDetailQueryOut](

[SalesOrderID] [int] NOT NULL,

[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,

[CarrierTrackingNumber] [nvarchar](25) NULL

)

C:\WINDOWS\System32>BCP [adventureworks2014].dbo.[SalesOrderDetailQueryOut] in C:\SOQueryOut.txt -S hqdbt01\SQL2017 -T -c

Format files

The BCP utility supports the use of a format file that contains the formatting details of each field in a file. The format file is used to provide all the required formatting details for bulk export and bulk import operations.

It provides a flexible way to interpret the data.
It provides an interface to re-format the data during the export process.
The format file eliminates the need of special programs for data import and export operations.

SQL Server supports two kinds of format files: XML format files and non-XML format files. The non-XML format file is the original format supported by earlier versions of SQL Server.

In this section we shall walk through some of the concepts of the format file, look at how to create the format file, and other details.

Let’s Jump in and get started:

CREATE TABLE SQLShackDemo

(

EID smallint,

EName nvarchar(50) NULL,

EJOB nvarchar(50) NOT NULL

);

INSERT INTO SQLShackDemo values(1,'Prashanth','DB Manager'),(2,'Bob','IT Director'),(3,'Scott','DevOp Eng');

Creating a Non-XML Format File

A non-XML format file is an ordinary file with a well-defined structure, and contains metadata about the column such as storage type, prefix length, field length, and field terminator.

The following BCP command is used to create format file:

1	BCP TestDatabase.dbo. SQLShackDemo format nul -c -f c:\BCPImport.fmt -t, -T

Version 10.0 is the version number of the BCP utility.
Number of columns is the number of fields in the data file; in this case, it’s 3.
The other fields in the format file describe the nature of the data fields.
Field Order indicates the position of each field.
Data Type describers the type of the data.
Prefix Len is the length for the prefix characters.
Data Len is the maximum allowed length for the data for the specific field.
Terminator is the delimiter used to separate any two fields of the data.
Column Order is the SQL Server table column order.
Column Name is the name of the SQL Server table column.
Column Collation is the collation used to store the characters in the data file.

If -f is used with the format option, the specified format file is created for the specified table or view. To create an XML format file, specify the -x option.

Let’s see an example to skip ENAME column of a table and load the data into the SQLShackDemoSkip table using a format file. Before changing the format file, let’s create the SQLShackDemoSkip table and a data file with EID and EJOB data using the SQLShackDemo table.

CREATE TABLE SQLShackDemoSkip

(

EID smallint,

EName nvarchar(50) NULL,

EJOB nvarchar(50) NOT NULL

);

Run the BCP command to generate the data file:

1	C:\WINDOWS\System32>BCP "select EID,EJOB from SQLShackDEMOATC.dbo.SQLShackDemo" queryout c:\SQLShackDemo3.txt -T -S HQDBT01\SQL2017 -c -t,

Following is the output of the data file:

Now, modify the format file, the first data field maps to EID, skips ENAME, and the third row maps the second data field of the data file to EJOB. The loading should ignore the ENAME column.

To skip a table column, modify the format file definition of the corresponding row and set the values to 0 for all the related columns as shown below.

Run the following BCP command to load the data into the SQLShackDemoSkip table.

1	C:\WINDOWS\System32>BCP SQLShackDemoATC.dbo.SQLShackDemoSkip in C:\SQLShackDemo3.txt -f C:\BCPformat.fmt -S hqdbt01\SQL2017 –T

The loading process excludes the second column of SQlShackDemoSkip.

1	SELECT * FROM SQLShackDemoSkip;

Summary

The BCP Utility, that’s very familiar to all SQL Server administrators, operates really well and is fast as well as efficient in terms of data import and export. Most of the options in the BCP command are case sensitive. It is recommended that we assume everything as case sensitive, so that we never run into troubles using the command.

BCP is capable of exporting and importing really large chunks of data, but if you’re exporting/importing data in the range of tens of millions, it’s recommended to break it into smaller chunks. And this can be using the SELECT statement with the WHERE clause. When we’re bringing the data in, we need to be cautious of the defined constraints. Sometimes we need to delete the constraint and dump the data, and then re-add those deleted constraints.

Getting started building applications using SQL Server DevOps Tools

Overview of SQLCMD utility in SQL Server

The BCP (Bulk Copy Program) command in action

Continuous Database Delivery (CD) using SQL Server Tools SqlPackage.exe

All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool

Getting started with SQL Operations Studio (SOS); initial installation and configuration

Author
Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

Stairway to SQL essentials - April 7, 2021
A quick overview of database audit in SQL - January 28, 2021
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021

BCP IN | OUT | QUERYOUT options

Format files

Creating a Non-XML Format File

Summary

Table of contents

Related posts: