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.