• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Export selected data from SQL Server to a tab file with bcp

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

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.

Check Out These Related posts:

  1. Create a CSV file from MySQL with PHP
  2. MySQL Control Center Returns 1000 Rows
  3. Create keyboard shortcut to export as PDF in Safari on OS X
  4. Export data from SQL Server to a tab file with bcp

Filed Under: Microsoft SQL Server

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap