Microsoft SQLServer Bulk Copy (BCP) Options

Bulk Copy (BCP) Options


Summary

This is just a quick summary of the bcp options that I use.


Detail

Exporting data

bcp "SELECT * FROM DATABASE..TABLE WHERE X = Y" queryout outfile.bcp -N -S 127.0.0.1 -U sa -P password

  • "SELECT * FROM DATABASE..TABLE" WHERE X = Y" - the query to get the rows you want
  • queryout - that the first parameter is a query and not a table name
  • outfile.bcp - the name of the file to put the data
  • -N - store the data with binary formats. If you use ASCII (-c option), things like CR/LFs and commas and such like may cause problems.
  • -S 127.0.0.1 - IP address of the server
  • -U sa - username
  • -P password - password

Importing data

bcp "DATABASE..TABLE" -N -S 127.0.0.1 -U sa -P password -h "TABLOCKX"

  • -h "TABLOCKX" put an exclusive lock on the table for the duration of the import. Can speed things up signifigantly.