Bulk insert and BCP usage

The Microsoft SQL Server data can be copied to or from a data file with the help of the bcp command prompt utility. Most of the times it is used to transfer large volumes of data from some other program, which is usually a database management system into a SQL Server table. First and foremost, the data is exported from the source program to a data file, and thereafter it is imported from the data file into a SQL Server table with the help of bcp. BCP can be used for transferring data to a data file for using in other programs from a SQL Server table. For example, one can copy the data from an instance of SQL Server into a data file and then, the data can be imported to another program.

With the help of the ODBC bulk copy application programming interface also known as API, the BCP utility is written. In its earlier versions, the DB-Library bulk copy API was used to write bcp utility.

With the help of a bulk insert statement, data can also be transferred into a SQL Server table from a data file. However, data cannot be bulk copied from an instance of SQL Server to a data file with the help of the BCP BULK INSERT statement. With the help of the functionality of the bcp utility with a Transact-SQL statement, the BULK INSERT statement allows one to bulk copy data to an instance of SQL Server, rather than from the command prompt.

One can also write programs to bulk copy SQL Server data to or from a data file using the bulk copy API. One can use the bulk copy API in OLE DB, SQL-DMO, ODBC, and DB-Library-based applications. Next comes Trigger Execution.

All of the bulk copy operations like the BULK INSERT statement, bcp utility, and the bulk copy API usually support a bulk copy hint, known as FIRE_TRIGGERS. INSERT and INSTEAD OF triggers are defined on the destination table and are executed for all rows inserted by the bulk copy operation if they are specified on a bulk copy operation that copies rows into a table. No bulk copy operations execute triggers, by default.

The following things are to be considered in order for bulk copy operations to specify FIRE TRIGGERS:

•Bulk copy operations which usually should be logged minimally, are fully logged.

•For each batch in the bulk copy operation, triggers are fired. All of the rows inserted by the batch are contained in the trigger passed by the inserted table.

•The client performing the bulk copy operation get no result sets generated by the insert triggers returned.

BCP and Bulk insert can be used in the above manner without any issues and solely for the purpose of finding solutions when copying data is a concern.