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.

Next: Using the bcp.exe utility >>
 

More SQL Server Articles
More By Mitchell Harper

Comments On: An Introduction To The Bulk Copy Utility

· Thanks a lot... U helped me a lot   >>> Post your comment now!