Using bcp and BULK INSERT

Using bcp and BULK INSERT

SQL Server 2000

The bcp command prompt utility copies Microsoft® SQL Server™ data to or from a data file. It is used most frequently to transfer large volumes of data into a SQL Server table from another program, usually another database management system (DBMS). The data is first exported from the source program to a data file, and then imported from the data file into a SQL Server table using bcp. Alternatively, bcp can be used to transfer data from a SQL Server table to a data file for use in other programs. For example, the data can be copied from an instance of SQL Server into a data file. From there, another program can import the data.

Note  The bcp utility is written using the ODBC bulk copy application programming interface (API). Earlier versions of the bcp utility were written using the DB-Library bulk copy API.

Data can also be transferred into a SQL Server table from a data file using the BULK INSERT statement. However, the BULK INSERT statement cannot bulk copy data from an instance of SQL Server to a data file. The BULK INSERT statement allows you to bulk copy data to an instance of SQL Server using the functionality of the bcp utility with a Transact-SQL statement, rather than from the command prompt.

It is also possible to write programs to bulk copy SQL Server data to or from a data file using the bulk copy API. The bulk copy API can be used in ODBC, OLE DB, SQL-DMO, and DB-Library-based applications.

Trigger Execution

All bulk copy operations (the BULK INSERT statement, bcp utility, and the bulk copy API) support a bulk copy hint, FIRE_TRIGGERS. If FIRE_TRIGGERS is specified on a bulk copy operation that is copying rows into a table, INSERT and INSTEAD OF triggers defined on the destination table are executed for all rows inserted by the bulk copy operation. By default, bulk copy operations do not execute triggers.

These considerations apply to bulk copy operations that specify FIRE_TRIGGERS:

  • Bulk copy operations that would usually be minimally logged are fully logged.

  • Triggers are fired once for each batch in the bulk copy operation. The inserted table passed to the trigger contains all of the rows inserted by the batch. Specify FIRE_TRIGGERS only when bulk copying into a table with INSERT and INSTEAD OF triggers that support multiple row inserts.

  • No result sets generated by the insert triggers are returned to the client performing the bulk copy operation.

See Also

SQL Server Backward Compatibility Details

Bulk-Copy Functions

Bulk-Copy Rowsets

BulkCopy Object


Performing Bulk Copy Operations

© 2016 Microsoft