Export selected data from SQL Server to a tab file with bcp

Posted September 9th, 2008 in Microsoft SQL Server

This post is a follow up to yesterday's post titled "Export data from SQL Server to a tab file with bcp". In this post we will again export data from a SQL Server table into a tab file but this time will select which data we want to export. This means you can export just a subset of data from a large table. You could also join multiple tables and export to one file.

In the example below we will export data from:

database "mydatabase"
table "mytable"
user: "myusername"
password: "mypassword"
server: "myhost"

into the file "mydata.csv". We want to only select data where someid = 55. You would do this for bcp (note I have added line breaks for readability, but this command should all be on one line):

bcp "select * from mydatabase.mytable.myusername where someid = 55"
    queryout mydata.csv
    /U myusername /P mypassword /S myhost /c

and for freebcp (again noting I have added linebreaks for readability - the command should either be on one line or you could put it on multiple lines on Linux as long as there's a \ at the end of each line):

bcp "select * from mydatabase.mytable.myusername where someid = 55"
    queryout mydata.csv
    U myusername /P mypassword /S myhost /c

As the data is exported the progress is displayed showing a line per thousand lines like so:

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

4251 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 4750   Average : (894.95 rows per sec.)

That will have exported only the data matching the select query into the mydata.csv file.

Share or Bookmark

Share or Bookmark this page using the following services. You will need to have an account with the selected service in order to post links or bookmark this page.

Subscribe or Follow

Subscribe via RSS or email, or follow me on Facebook or Twitter below. The RSS icon takes you through to Feedburner where you can select the service or application to use.