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.

I'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 latets SQL Server Service Pack

     I'm not going to make your life miserable SP1 for SQL 2008 R2 is found here.

Use BCP with a Custom Query.

       BCP has 3 option. 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