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

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
TABLE OF CONTENTS:
  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
SEARCH DEVARTICLES

An Introduction To The Bulk Copy Utility - What is bulk copy?
(Page 2 of 5 )

Contrary to what many new SQL developers think, bulk copy isn't the bcp.exe utility that ships with SQL Server. Bulk Copy is an application programming interface (or API for short) that allows us to interact with SQL Server to export and import our data in one of two data formats.

The bulk copy API is exposed through two different data access libraries: DB-LIB and the common ODBC (Open Database Connectivity) format. This API contains dozens upon dozens of routines that we can use to perform everything from exporting data into a flat file to importing data from a totally separate SQL server.

So why would we want to use bulk copy over other methods of importing/exporting data into SQL Server?:
  • Bulk copy is fast: When running bulk copy in non-logged (i.e. non-transactional) mode, it can easily import/export thousands of rows per second.
  • Bulk copy is powerful: If you run the bcp.exe utility from the command line then you will see that there are dozens of switches that can be used to specify how data is treated during the import/export process. These switches can be used to precisely control how and where the data is imported to or exported from.
  • Bulk copy is flexible: Using bulk copy, you can easily export from either a table or from the results of a TSQL query.
While it's possible to call the bulk copy API directly from a programming language like C++, in this article we're going to concentrate on one method for using the functionality of this API: the DOS based bcp.exe utility.

Before we look at this method however, there are certain terms that we must understand as we will be using them later in this article:
  • BCP: Refers to one or more implementations of the bulk copy API, either directly (such as by calling the API from C++) or indirectly (such as using the bcp.exe utility).
  • Data file: A data file is a source/destination file from where data for the database is either read or written. For example, the file that bulk copy “dumps” the data to is a data file. Note that source files and destination files are also classified as data files, just with different names.
  • Batch size: If importing/exporting data with bulk copy, it will, by default import/export all rows in one go. By modifying the batch size, we can limit the number of rows that are processed per batch (or cycle).
  • File format: SQL Server and bulk copy can work with two different file formats: native format, which is a proprietary SQL Server binary format, or character format, which is simply composed of characters from the common ASCII character set.
To successfully perform a bulk copy operation, we also need sufficient system credentials. Those credentials required are shown below:
  • When importing rows from an exported file, we must have INSERT permissions on the destination table.
  • When exporting rows from a table, we must have SELECT permissions on the source table. Because bulk copy queries the structure of the underlying table, we also need SELECT permissions on these 3 system tables: sysindexes, sysobjects and syscolumns.
If you are running SQL Server from a fresh install or you haven't modified any user accounts or permissions then you will automatically have all of the required privileges to successfully work with bulk copy.

Now that we understand a little more about bulk copy, let's take a look at the two methods that we can use to work with its API.
blog comments powered by Disqus
SQL SERVER ARTICLES

- 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 
Support 

Developer Shed Affiliates

 




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