Using BCP command to Copy data from SQL table to files

Let us say we have data in one of our SQL tables and we want to copy the data into a file and store it either in our local or on any server.

What kind of a file? Well, the data file format can be anything; a normal text, comma separated  value (popularly called CSV), pipe delimited value (each value would be separated by a ‘pipe -|’). It all depends on what you want to do with the file after copying.

I want to do this manually!Then you could do it in the following way (by the way, we are using SQL Server):

  1. Open SQL Server Management Studio (SSMS), choose Server type as Database Engine, Type the Server Name, choose your Authentication mode (Windows or SQL) and finally hit Connect.
  2. Choose New Query and write a select statement to query data from your table as below:

USE MY_DATABASE
SELECT * FROM MY_TABLE

and execute.

  1. Make sure Results to Grid (Ctrl + D) is selected, In the Results tab Right Click, Select All, Copy with headers and paste it in any text file. That’s it!
    Disadvantages of doing it manually are:

  1. Well, it is a manual process.
  2. Difficult to perform if the table has huge number of records.
  3. By default it is tab delimited (columns are separated by tabs), so if we are required to make a CSV or pipe delimited format it takes more time and is very tedious to do.

Oh, then how do we do it programmatically?  We will use a windows command file approach in this post. Reason being it is simple, doesn’t need any additional software installations and such.

Specifically, we would use a BCP (Bulk Copy Program) command to achieve this.

SYNTAX:
BCP [Source Table | Query]  {OUT | QUERYOUT}  [Destination File with its path] [… Parameters]

    Below is how we do it.

  1. Open Notepad or any other text-editing software.
  2. Write the below code:


SET Server= ServerName
SET DB=Databasename
SET FilePath=D:\PracticeFolder
SET BCPTable=MyTable
SET OutputFile=file.txt
BCP %BCPTable% OUT %FilePath%\% OutputFile% -S%Server% -d%DB% -T -c -t

  1. Save the file as BCPTest.cmd
  2. Double click on this file to execute it.
    General Notes:

  1. ServerName: If you are using this to test in your local server, use a dot (.). Otherwise, Server Name.
  2. FilePath: Use the path where u want to generate the output file.
  3. OUT parameter is used when we BCP out an entire table.
  4. QueryOut is used when we need to BCP the result set of a select query.
  5. Variable names are enclosed within % symbol in batch files. Like %Variable Name%
    Notes about BCP Parameters:

  1. –S is used to specify the name of the server.
  2. –d is used to specify the name of the database, where we have our table.
  3. –T is used to specify that our SQL connection is secure/Trusted.
  4. –c is used to specify that we use character data type in this operation.
  5. –t is used to specify the data type of the output file.
  6. –t by default is for tab delimited.
  7. –t, is for Comma Separated value.
  8. –t^| is for pipe delimited values.
    Examples:

  1. Tab delimited: BCP %BCPTable% OUT %FilePath%\% OutputFile% -S%Server% -d%DB% -T -c -t
  2. Pipe delimited: BCP %BCPTable% OUT %FilePath%\% OutputFile% -S%Server% -d%DB% -T -c –t^|
  3. Comma delimited: BCP %BCPTable% OUT %FilePath%\% OutputFile% -S%Server% -d%DB% -T -c –t,

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s