SQL Server BCP Utility Experts Guide.

What is BCP?

According to MSDN  "The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns". The official documentation  for BCP found on this link. and  I do not intend to replace it with this write up.

We've written this guide to aid BCP developer for their extreme needs. Below are some common scenarios and challenges  you might encounter writing a BCP solution.

Before You Begin

1.** Know which BCP version you are trying to use.   **
     If you have several SQL Server installed on your PC, it would help  you a lot to know which version of BCP  you are trying to use.

      For SQL Server 2008 the BCP is located on this folder:

 *C:\Program Files\Microsoft SQL Server\100\Tools\Binn

*A short review of DOS. You need to issue a CD [change directory] command to point to this path. Otherwise you are working with unknown version of BCP. There might be several of them.

C:\CD\Program Files\Microsoft SQL Server\100\Tools\Binn

 

2.  Do not use the root folder for output
           Later version of Windows does not allow you to write on the root folder unless you are running with elevated permission
      so I suggest that you better make a [c:\bcp] folder

3.  Do download and install the latest SQL Server Service Pack
**
**     SP1 for SQL 2008 R2 is found here.

Use BCP with a Custom Query

       BCP has three options: in, out, and queryout. If you want to use  a custom query you should use the queryout option.

        Example:
         *C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp "SELECT * FROM Adventureworks.Production.BillOfMaterials" QUERYOUT c:\bcp\PersonAddressByQuery.txt -T

*

Output a Humanly Readable Result

BCP output by default are not humanly readable. To make it humanly readble you have to add the -c switch. And be careful of the switches because they are care sensitive.

**Example: SQL Statement Execution
***bcp "SELECT * FROM Adventureworks.Production.BillOfMaterials" QUERYOUT d:\PersonAddressByQuery.txt -T -c

*

 **Example:  SP  Execution
***bcp " EXEC Adventureworks.Production.ProceduresalesDetails" QUERYOUT d:\QueryOut.txt -T -c
*

 

Add Field Header

BCP output does not have header but it can be worked around.  First you can output the data file which
contains the BCP Data and then you can out put the header on the second file then you can use
the copy command to merge the two documents.

Example:
**
     1.  This will write the heading:**

*                BCP "select 'AddressID','AddressLine1','AddressLine2','City','StateProvinceID','PostalCode','rowguid','ModifiedDate'" queryout c:\bcp\heading1.csv  -c  -T -S. -t"|"
*
**      2.  This will write the data:**

*                BCP " select AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode ,rowguid,ModifiedDate from AdventureWorks.Person.address" queryout c:\bcp\body.csv  -c  -T -S. -t"|"

*  **   3. This will combine the heading and the data to address CSV**
**
***             c:>cd/bcp
             c:/bcp> copy /b heading.csv+body.csv c:/bcp/Address.csv*

Use Text Qualifier

Text qualifier is another option that is not available to BCP but it can be worked around as well. In the BCP
query you can make use of the TSQL function Quotename. Which surronds the field with quotes or what ever charater
you want functioning very closely to the SSIS Text Qualifier.

** Example:**
**      ** BCP " select quotename(AddressID,CHAR(34)),quotename(AddressLine1,CHAR(34)),quotename(AddressLine2,CHAR(34)) from AdventureWorks.Person.address" queryout C:\bcp\temp2.csv -c -T -S. -t"|" 

Connect To a SQL PORT other than the Default

If you are connecting to a SQL Server instance with a modified port like MYSERver\myinstance:25557 - S switch will not work. Instead, you need to create an alias for that server on the SQL Configuration Manager and you can make use of that alias.