Home | About Chris Hope | Contact Chris Hope | Search:

Export data from SQL Server to a tab file with bcp

Posted September 8th, 2008 in Microsoft SQL Server

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.

Comments

blog comments powered by Disqus