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)
