Performing Bulk Copy Operations

The Microsoft® SQL Server™ bulk copy feature supports the transfer of large amounts of data into or out of a SQL Server table or view. Data can also be transferred out by specifying a SELECT statement. The data can be moved between SQL Server and an operating-system data file, such as an ASCII file. The data file can have different formats; the format is defined to bulk copy in a format file. Optionally, data can be loaded into program variables and transferred to SQL Server using bulk copy functions. This is typically much faster than using INSERT statements or calling SQLBulkOperations with SQL_ADD.

The ODBC standard does not directly support SQL Server bulk copy operations. When connected to SQL Server version 6.0 or later, the SQL Server 7.0 ODBC driver supports the DB-Library functions that perform SQL Server bulk copy operations. This driver-specific extension provides an easy upgrade path for existing DB-Library applications that use bulk copy functions. The specialized bulk copy support is in the following files:

• Odbcss.h

  Includes function prototypes and constant definitions for bulk copy functions. Odbcss.h must be included in the ODBC application performing bulk copy operations and must be in the application’s include path when it is compiled.

• Odbcbcp.lib

  Must be in the library path of the linker and specified as a file to be linked. Odbcbcp.lib is distributed with the SQL Server ODBC driver.

• Odbcbcp.dll

  Must be present at execution time. Odbcbcp.dll is distributed with the SQL Server ODBC driver.

An application typically uses bulk copy in one of the following ways:

• Bulk copy from a table, view, or the result set of a Transact-SQL statement into a data file where the data is stored in the same format as the table or view.

  This is called a native-mode data file.

• Bulk copy from a table, view, or the result set of a Transact-SQL statement into a data file where the data is stored in a format other than the one of the table or view.

  In this case, a separate format file is created that defines the characteristics (data type, position, length, terminator, and so on) of each column as it is stored in the data file. If all columns are converted to character format, the resulting file is called a character-mode data file.

• Bulk copy from a data file into a table or view.

  If needed, a format file is used to determine the layout of the data file.

• Load data into program variables, then import the data into a table or view using the bulk copy functions for bulk copying in a row at a time.

Data files used by bulk copy functions do not have to be created by another bulk copy program. Any other system can generate a data file and format file according to bulk copy definitions; these files can then be used with a SQL Server bulk copy program to import data into SQL Server. For example, you could export data from a spreadsheet in a tab-delimited file, build a format file describing the tab-delimited file, and then use a bulk copy program to quickly import the data into SQL Server. Data files generated by bulk copy can also be imported into other applications. For example, you could use bulk copy functions to export data from a table or view into a tab-delimited file that could then be loaded into a spreadsheet.

Programmers coding applications to use the bulk copy functions should follow the general rules for good bulk copy performance. For more information, see Factors Affecting Bulk Copy Performance.

Note The ODBC SQLBulkOperations function has no relationship to the SQL Server bulk copy functions. Applications must use the SQL Server-specific bulk-copy functions to perform bulk copy operations.