Home arrow SQL Server arrow Page 3 - An Introduction To The Bulk Copy Utility

An Introduction To The Bulk Copy Utility

There are a number of ways to import and export data with SQL Server 2000. Bulk copy is one of them. In this article Mitchell introduces us to basic import/export functionality with the bulk copy command utility.

Author Info:
By: Mitchell Harper
Rating: 4 stars4 stars4 stars4 stars4 stars / 98
June 12, 2002
  1. · An Introduction To The Bulk Copy Utility
  2. · What is bulk copy?
  3. · Using the bcp.exe utility
  4. · Bcp.exe Example #1
  5. · Conclusion

print this article

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:

Running bcp.exe at the command line

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.
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables – New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials