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.
Step 1: Open Command Prompt
Go to run and type cmd to open command prompt in your system.
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
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
Step 4: Open the output file
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)
20 Comments. Leave new
Nicely explained with examples.
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!
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.
you can use SQLCMD for that.
can I have some examples or reference links to get it done in more professional way.
How do i get it to export the data and the column headers?
is any way to call bcp command from sql server management studio? if yes then how?
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?
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
Bcp ” my select statement” queryout filelocation -t -c was used for out
Hi Dave, on step 2 you typed sql 2015 and ir should be sql 2016 donde versión is 130
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
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 ?
-T (trusted connection) is specifying to use the Windows authentication. So do we still need the -U and -P?
Is there any Way to generate the file on developer machine, not on the sql server machine
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.
Hello, Is there an easy way to export a table with all columns (headers). Whenever I Issue bcp command I am getting the records without headers .
Hint, pick the only command that doesn’t have a problem finding the server, and then do what you did with all the other commands that you did not on this command. “”