Export selected data from SQL Server to a tab file with bcpExport 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.

Recent posts: