The bcp command line tool allows you to import and export data from a SQL Server database into a text file. There is also a freebcp tool from the FreeTDS library so you can do this from Linux/Unix boxes as well. This post looks at how to export data from SQL Server into a tab file using bcp/freebcp.
To export data from:
- database “mydatabase”
- table “mytable”
- user: “myusername”
- password: “mypassword”
- server: “myhost”
into the file “mytable.csv” you would do this for bcp:
bcp mydatabase.dbo.mytable out mytable.csv /U myusername /P mypassword /S myhost /c
and like this for freebcp:
freebcp mydatabase.dbo.mytable out mytable.csv -U myusername -P mypassword -S myhost -c
The resulting output should look something like this:
Starting copy... 1000 rows successfully bulk-copied to host-file. Total received: 1000 1000 rows successfully bulk-copied to host-file. Total received: 2000 1000 rows successfully bulk-copied to host-file. Total received: 3000 1000 rows successfully bulk-copied to host-file. Total received: 4000 1000 rows successfully bulk-copied to host-file. Total received: 5000 1000 rows successfully bulk-copied to host-file. Total received: 6000 6587 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 6156 Average : (1070.01 rows per sec.)
This will export all the data from the table into the text file. In a post tomorrow I will look at how to do the same but with a query to specify which data you would like to export, which is useful if you only want a small subset of data from a large table.
