http://www.sqlservercentral.com/blogs/salvoz-sql/2013/03/04/introduction-to-the-sql-bcp-utility/

Printed 2014/10/20 04:57AM

Introduction to the SQL BCP Utility

By Jennifer Salvo, 2013/03/04

SQL Server BCP is a command line utility that bulk copies data between instances of SQL Server and data files.  Data can be imported into SQL Server tables or exported to a data file.  The BCP utility can also be used to generate format files which specify the number and type of data columns.  The BCP utility can quickly transfer large amounts of data.

The syntax of a BCP command is shown below:

image

The first argument specifies the data source or destination.  Data can be exported directly from a table, view or query and imported into a table or view.

The second argument specifies the direction of the bulk copy.  The options are:

<data_file> is a .txt file that stores the data to be imported/exported.

Definitions of some useful arguments are included below:

The following are some usage examples:

bcp <DB>.[dbo].<Table> in “<DataFile>.txt” -f “<FormatFile>.fmt” –T

Imports data into table <Table> from data file <DataFile>.txt using a trusted connection and format file <FormatFile>.fmt

bcp <DB>.[dbo].<Table> format nul -c -f <FormatFile>.fmt -t #$# -r #@# –T

Creates a format file based off of the table <Table> using the SQLCHAR data type (-c argument).  The name of the format file is specified as <FormatFile>.fmt , #$# is the field terminator, and #@# is the row terminator. –T specifies a trusted connection.

bcp “SELECT [Col1], [Col2], [Col3],… FROM <Table>” queryout “<DataFile>.txt” -c -t #$# -r #@# -T

Exports data using specified query into data file <DataFile>.txt using a trusted connection.  #$# is the field terminator, and #@# is the row terminator.

Recommended Practices:

A good blog post discussing the BCP utility:  https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.