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.
