An Introduction To The Bulk Copy Utility - Using the bcp.exe utility
(Page 3 of 5 )
Bcp.exe is the command-line interface to the bulk copy API. Bcp.exe is dumb. That's right. It simply analyses the switches and parameters that we pass to it and makes an informed call to the underlying bulk copy API, which does all of the hard work.
Bcp.exe is installed with SQL Server, and resides in the following directory...
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
... if you didn't modify the default SQL installation locations. The path to bcp.exe is automatically setup when SQL Server is installed, so it's just a simple matter of dropping to a command prompt and typing :bcp" to run it. Here's what the output looks like on my PC:
As you can see, without specifying any options, bcp.exe spits out the various types of switches that it supports as well as their uses. The syntax for calling bcp.exe with switches looks rather freaky, however next to none of the switches are required to perform basic data import/exports:
bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
[-q] [-C code_page] [-t field_term] [-r row_term]
[-i input_file] [-o output_file] [-a packet_size]
[-S server_name[\instance_name]] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]
Each of these switches has a special meaning to bcp.exe. A basic description of the most common switches is shown below:
These switches are case sensitive
- {in | out | queryout | format}: Tells bcp.exe which type of operation to perform. "In" tells bcp.exe that we are importing data. "Out" is for exporting data. "Queryout" tells bcp.exe that we are specifying the data to be exported as a TSQL query. This query should be surrounded with double quotes. "Format" tells bcp.exe that we will be using a format file to specify the layout of the data (Format files aren't required for basic operations, so we won't be looking at them in this tutorial).
- datafile: Specifies the name of the data file to import/export from/to.
- -m: The maximum number of errors that can occur before bcp.exe terminates. This value is 10 by default, meaning that any bcp.exe operation will terminate if 10 errors occur during a single session.
- -F: Tells bcp.exe the number of the first row to start copying from.
- -b: Allows us to specify the size that each batch should be in terms of the number of rows.
- -n: Tells bcp.exe that we want to work with data in SQL Server’s native binary format.
- -c: Tells bcp.exe that we want to work with data in plain ASCII character mode.
- -T: Tells bcp.exe that it should attempt to login to SQL Server with a trusted connection.
- -U: If not working with a trusted connection, this switch followed by a valid SQL Server account username tells bcp.exe the username part of your login credentials.
- -P: Used in combination with the –U switch followed by a password.
Don’t get confused by the large number of switches that bcp.exe accepts, because it can be extremely easy to use it to perform import/export routines on your data, as we're about to see.
These examples work with the Northwind database, which is installed as an SQL Server database.
Next: Bcp.exe Example #1 >>
More SQL Server Articles
More By Mitchell Harper
/ 77 
