Programming4us
         
 
 
SQL Server

SQL Server Integration Services : Using bcp (part 1)

11/30/2010 3:10:22 PM
As you have seen in this article, it is fairly easy to create and implement SSIS packages to do data transformations from one or more data sources to one or more data destinations. However, many organizations still really just need a vanilla and very fast mechanism to export data out of SQL Server or import data into SQL Server. bcp fills this need well (and has done so from the beginning of SQL Server).

The following sections outline the primary execution methods of bcp, the many switches of bcp, the format file, and ways to improve performance when using bcp. By the end of these sections, you will be able to optimally execute bcp successfully for several common production scenarios. Microsoft has added a new execution switch that generates an XML format file. Most other features of bcp have remained the same.

First, you need to see whether you have the right version of bcp. A quick check of your version of bcp guarantees that you won’t run into any limitations from older versions of bcp that might be left on your servers. You can do this by executing bcp at the command prompt with the –v option and no other parameters. (Note that all bcp switch options are case sensitive; for example, –v and –V are two very different switches.) Here’s an example:

C:> bcp –v
BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (C) Microsoft Corporation. All Rights Reserved.
Version: 10.0.1600.22

This is version 10.0, which is distributed with MS SQL Server 2008 (SQL Server 10.0). If a version other than 10.x is present here, you must re-install bcp immediately.

At any time, you can see the proper usage and bcp switch options available by executing bcp at the command prompt with a question mark (?):

C:> bcp ?
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
. . .

You use the following syntax for bcp, along with one or more switches:

bcp {dbtable | query} {in | out | queryout | format} datafile

In this syntax, dbtable is the database_name, schema, and table_name | view_name (for example, AdventureWorks2008.Production.Product or "AdventureWorks2008.Production.Product"):

  • database_name This is the name of the database in which the specified table or view resides. If not specified, this is the default database for the user.

  • owner This is the name of the schema of the table or view.

  • table_name | view_name This is the name of the destination table or view when copying data into SQL Server (in), and it is the name of the source table when copying data from SQL Server (out).

query is a T-SQL query that returns a result set. queryout must also be specified when bulk-copying data from a query.

in | out | queryout | format specifies the direction of the bulk copy (in copies from a file in to the database table or view, out copies from the database table or view to a file). queryout must be specified when bulk-copying data from a query. format creates a format file based on the switch specified (–n, –c, –w, –V, or –N) and the table or view delimiters. If format is used, the –f option must be specified as well.

data_file is the data file used when bulk-copying a table or view into or out of SQL Server.

All the available bcp switches are listed in Table 1.

Table 1. bcp Switches
SwitchDescriptionExample
-mSpecifies the maximum number of errors to allow before stopping the transfer. The default is 10.[–m max_errors]
-fSpecifies the format file used to customize the load or unload data in a specific style.[-f format_file]
-eSpecifies the file to write error messages to.[-e err_file]
-FSpecifies the first row in the data file to start copying from when importing. The default is 1.[-F first_row]
-LSpecifies the last row in the data file to end copying with when importing. The default is 0, which indicates the last row in the file.[-L last_row]
-bSpecifies the number of rows to include in each committed batch. By default, all data rows in a file are copied in one batch.[-b batch_size]
-nSpecifies that native (database) data type formats are to be used for the data.[-n]
-cSpecifies that character data type format is to be used for the data. In addition, \t (tab character) is used as the field separator, and \n (newline character) is used as the row terminator.[-c]
-wSpecifies that the Unicode data type format is to be used for the data. In addition, \t (tab character) is used as the field separator, and \n (newline character) is used as the row terminator.[-w]
-NSpecifies to use Unicode for character data and native format for all others. This can be used as an alternative to the –w switch.[-N]
-VSpecifies to use data type formats from earlier versions of SQL Server.[-V (70 | 80|90)]
-qTells bcp to use quoted identifiers when dealing with table and column names.[-q]
-CIf you are loading extended characters, allows you to specify the code page of the data in the data file.[-C code_page]
-tSpecifies the terminating character(s) for fields. The default is \t (tab character).[-t field_term]
-rSpecifies the terminating character(s) for rows. The default is \n (newline character).[-r row_term]
-iSpecifies a file for redirecting input into bcp (the response file containing the responses to the command prompts).[-i input_file]
-oSpecifies the file for receiving redirected output from bcp.[-o output_file]
-aSpecifies the network packet size (in bytes) used to send to or receive from SQL Server. Can be between 4,096 and 65,535 bytes. The default size is 4,096.[-a packet_size]
-SSpecifies the SQL Server name to connect to. Local is the default.[-S server_name | server_name\instance_name]
-USpecifies the user account to log in as; this account must have sufficient privileges to carry out either a read or a write of the table.[-U login_id]
-PSpecifies the password associated with the user account.[-P password]
-TMakes a trusted connection to the server, using the network user/security credentials instead of the login_id/password.[-T]
-vDisplays the bcp version information.[-v]
-RUses the regional format for currency, date, and time data, as defined by the locale settings of the client computer.[-R]
-kOverrides a column’s default and enforces NULL values being loaded into the columns as part of the bcp operation.[-k]
-EUses the identity values in the import file rather than generating new ones.[-E]
-hSpecifies special hints to be used during the bcp operation. They include the following: the sort order of the data file, number of rows of data per batch, number of kilobytes of data per batch, whether to acquire a table-level lock, whether to check constraints, and whether to fire insert triggers.[-h hint_type,..]
-xGenerates an XML format file.[-x]

 

.
Other -----------------
- SQL Server Integration Services : Connection Projects in Visual Studio
- SQL Server Integration Services : The Package Execution Utility (part 3) - The dtutil Utility
- SQL Server Integration Services : The Package Execution Utility (part 2) - Running Packages
- SQL Server Integration Services : The Package Execution Utility (part 1)
- SQL Server Integration Services : The SSIS Designer
- SQL Server Integration Services : Running the SSIS Wizard
- SQL Server Integration Services : A Data Transformation Requirement
- SQL Server 2008 : SSIS Tools and Utilities
- SQL Server 2008 : SSIS Architecture and Concepts
- SQL Server 2008 : SQL Server Integration Services - SSIS Basics
- Defensive Error Handling : Using Transactions and XACT_ABORT to Handle Errors
- Managing Security Within the Database Engine : Securables
- Managing Security Within the Database Engine : Database Security
- Managing Security Within the Database Engine : Creating SQL Server Principals
- SQL Server 2008 : Performance Tuning - Locks, Blocking, and Deadlocks
- SQL Server 2008 : Performance Tuning - Tracing
- SQL Server 2008 : Implementing Error Handling - Managing and Raising User-Defined Errors
- SQL Server 2008 : Implementing Error Handling - Understanding Errors
- Implementing SQL Server Objects Using Managed Code (part 2)
- Implementing SQL Server Objects Using Managed Code (part 1)
 
 
Most View
- iPad SDK : Preparing Dudel for a New Tool (part 5) - Rendering Multiple Styles
- SOA with .NET and Windows Azure : WCF Discovery (part 1) - Discovery Modes
- jQuery 1.3 : Developing plugins - Method parameters
- Windows Phone 7 : Pinning Favorites to Start
- Sharepoint 2010 : Content Management - Importing a Term Set
- SharePoint 2010 : Create a Personal Site
- Windows 7 : Connecting to the Remote Desktop (part 1) - Making a Basic Connection
- Security in Cloud Computing (part 2) - Identity and Access Management
- Windows 7 : Working with Users and Groups from the Command Line
- What's New in SharePoint 2013 (part 1) - THE PROGRAMMING MODEL
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS