SQL SERVER – Simple Example of BCP Command Line Utility

I was recently asked if I know the fastest way to export data from SQL Server table. This was very interesting question personally, as I have not run any such tests in the past. However, everytime when I had to export data, I have used in past BCP command line utility and I have found it does a pretty good job.

In this blog post we will see a working example of how BCP works.

SQL SERVER - Simple Example of BCP Command Line Utility bcp5-800x448

Step 1: Open Command Prompt

Go to run and type cmd to open command prompt in your system.

SQL SERVER - Simple Example of BCP Command Line Utility bcp1

Step 2: Change your directory context

Change your directory context to the folder where BP Utility is located

BCP Location for SQL Server 2012 – C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn

BCP Location for SQL Server 2014 – C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\120\Tools\Binn

BCP Location for SQL Server 2015 – C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn

SQL SERVER - Simple Example of BCP Command Line Utility bcp2

Step 3: Run BCP Command Line Utility

In following example, I am exploring Person. Address table of AdventureWorks2014 database. My server’s IP address is 127.0.0.1 and username is sa and password is sql. I hope this simple example helps to understand how BCP works.

bcp "Person.Address" out c:\data\Address.txt -S "127.0.0.1" -d AdventureWorks2014 -Usa -Psql -c -T

SQL SERVER - Simple Example of BCP Command Line Utility bcp3

Step 4: Open the output file

SQL SERVER - Simple Example of BCP Command Line Utility bcp4

That’s it! We are done!

As I have said, I have not done any tests, but I have found BCP to extremely fast when I have to export data and I suggest you try this out as well.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – SSMS Trick – Switching Columns in Grid Output
Next Post
PowerShell – Create a User with Read Rights on a Database

Related Posts

18 Comments. Leave new

  • Nicely explained with examples.

    Reply
  • Jonathan MacCollum
    October 1, 2016 1:50 am

    Oh no Dave! Use of sa account in examples leads to lots of bad practices :( use integrated security and it’s even faster because you don’t type a un/pw and you don’t have to compromise a secure instance that’s configured using Windows authentication only best practice!

    Reply
  • is that Possible to execute the procedure wih params and export the output to csv format using BCP commands.

    Note: my procedure can take upto 4 hours to execute and return rows upto 8Lac records.

    Reply
  • can I have some examples or reference links to get it done in more professional way.

    Reply
  • Gian Carvajal
    April 14, 2017 3:54 am

    How do i get it to export the data and the column headers?

    Reply
  • is any way to call bcp command from sql server management studio? if yes then how?

    Reply
  • jacksoncoutinho
    July 11, 2017 2:55 pm

    Not able to generate BCP file when out file name consists on Unicode characters.

    bcp “[MyDB].[dbo].MyTable” out “c:מבקר_abc.Txt” -w -t$$$$!@** -r#n -Ssysname1 -Usa -Psapass -b5000

    How should I do this?

    Reply
  • Hello, I am trying to import back dat file which was generated by bcp queryout command but it is failing with truncate error. This is for SQL server 2005

    Reply
  • Hi Dave, on step 2 you typed sql 2015 and ir should be sql 2016 donde versión is 130

    Reply
  • DECLARE @bcp_cmd1 VARCHAR(1000);
    DECLARE @exe_path1 VARCHAR(200) =
    ‘ cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ & ‘;
    SET @bcp_cmd1 = @exe_path1 +’ BCP.EXE db_zar.Z_Master_City out D:\DEPTLIST1.CSV -T -c -t, -r \n ‘;
    EXEC master..xp_cmdshell @bcp_cmd1;
    GO

    my command is above in query, when i run it on another machine its perfect, but when on server
    Error is on server

    output
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file
    NULL

    Reply
  • Good explanation….if my understanding is correct can use BCP command to export data from sybase and use the data file ti import data to SQL server for data migration between sybase & sql server ?

    Reply
  • -T (trusted connection) is specifying to use the Windows authentication. So do we still need the -U and -P?

    Reply
  • Is there any Way to generate the file on developer machine, not on the sql server machine

    Reply
  • Trying to run bcp from windows command prompt against SQL server 2017 on Windows 10. All attempts unsuccessful.

    Just downloaded “MS Command line utilities 15 for sql server” which also includes bcp. The sql server uses windows authentication.

    Here are some examples of commands and corresponding errors:

    bcp DB..table in filename.dat -c -t “|” -T -S
    CTLIB Message: – L6/O8/S5/N3/5/0:
    ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
    Establishing connection failed.

    bcp DB..table in filename.dat -c -t| -T -Sservername\instance
    ‘-T’ is not recognized as an internal or external command,
    operable program or batch file.

    bcp DB..table in filename.dat -c -t “|” -Uuser -Ppassword -SNNN.NN.NNN.NN
    CTLIB Message: – L6/O8/S5/N3/5/0:
    ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
    Establishing connection failed.

    bcp DB..table in filename.dat -c -t “|” -T -SNNN.NN.NNN.NN
    Password:
    CTLIB Message: – L6/O8/S5/N3/5/0:
    ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
    Establishing connection failed.

    bcp DB..table in filename.dat -c -t “|” -Uuser -Ppassword -S”NNN.NN.NNN.NN”
    CTLIB Message: – L6/O8/S5/N3/5/0:
    ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
    Establishing connection failed.

    bcp DB..table in filename.dat -c -t “|” -T -S”NNN.NN.NNN.NN”
    Password:
    CTLIB Message: – L6/O8/S5/N3/5/0:
    ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
    Establishing connection failed.

    Reply

Leave a Reply

Menu