Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.

Introduction to the SQL BCP Utility

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:

  • in <data_file>: copies data from <data_file> to the specified table or view.
  • out <data_file>: copies data from the specified table or view into <data_file>.
  • queryout <data_file>: copies data from a query into <data_file>.
  • formal nul: creates a format file based on the option specified (-n, -c, -w, or -N) and table or view.

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

Definitions of some useful arguments are included below:

  • -c: Performs an operation using the character data type.  Note: by default \t (tab) is the field separator and \r\n (newline character) is the row terminator.  If your data may contain either of these characters, you should override the field separator and row terminator by using the –r (row term) and –t (field term) arguments.
  • -e <err_file>: Specifies the path to an error file location that stores rows the bcp utility could not transfer.
  • -f <format_file>: Specifies the full path of a format file.  Can be used in two ways:
    • If -f is used with the format option, the specified format_file is created for the specified table or view.
    • If used with the in or out option, -f requires an existing format file.
  • -n: Performs the bulk-copy operation using the native (database) data types of the data.
  • -N: Performs the bulk-copy operation using the native (database) data types of the data for non-character data, and Unicode characters for character data. This option offers a higher performance alternative to the -w option, and is intended for transferring data from one instance of SQL Server to another using a data file.
  •  -r <row_terminator>: Specifies the row terminator. The default is \n (newline character).
  • -t <field terminator>: Specifies the field terminator. The default is \t (tab character).
  • -T: Specifies the bcp utility connects to SQL Server with a trusted connection using integrated security.
  • -w: Performs the bulk copy operation using Unicode characters.

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:

  • Use the native format (-n) when possible to export and import using SQL Server.  If the data will be imported to another type of database, use the –c or –w option.
  • Consider overriding the row and field default terminators (the default values are \t (tab) for the field terminator and \r\n for the row terminator ) with random hexadecimal values.  Override the field terminator using the –t flag and the row terminator using the –r flag.

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

Comments

Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...