BCP Overview
From SQLServerPedia
|
See Also: Main_Page - Code Management - Loaders - Bulk Copy Program (BCP) Bulk Copy Program - command line data import/export utility - became available with SQL Server 6.0. It is a command line utility that lets you import and export data from SQL Server to text file format and the other way around. To reduce locking contention on the destination table, it is recommended to use BCP at hours of limited user activity if at all possible. Before Data Transformation Services debuted with SQL Server 7.0 BCP was the tool of choice for loading data. Compared to DTS, BCP is quite limited. However, BCP has not lost its significance and at times can be the most convenient or the fastest way of transferring data. With SQL Server 7.0 and later, BCP uses ODBC interfaces instead of DB-Library. Therefore, if you have to work in an environment with various versions of SQL Server, be sure to read documentation closely for notable differences in certain data type handling. Click here for the BCP Syntax. Unless specified otherwise, BCP ignores all existing table constraints - including foreign keys and triggers. This significantly speeds up the loading process. There are a couple of options to overcome the limitation of ignoring constraints. First, you can specify the "CHECK CONSTRAINTS" hint to override the default behavior and, effectively degrade performance. You can also specify the hint "FIRE TRIGGERS", which will override the default BCP behavior of ignoring any triggers on the destination table. The other option is to load data fast and then run DBCC CHECKCONSTRAINTS to find out whether your data load violated any referential integrity rules. Which of these options you should use depends on the situation. When you need to quickly populate a table to make it available for querying, it probably makes sense to ignore all constraints and run the DBCC statement during off hours. However, if you have an OLTP application that needs to be highly consistent, it might be a good idea to use the "CHECK CONSTRAINTS" option, wait a while longer and have all the constraints enforced. In SQL Server 2000 by default BCP is a logged operation. You may wish to change this default behavior to avoid BCP logging and therefore filling up the transaction log. To do so, you will have to satisfy following conditions:
"TABLOCK " will lock the destination table for the duration of the data load. This will improve the speed of bulk copy; however, it will not let any other users work with that table. Another way to alter BCP behavior on the destination table is to use the SP_TABLEOPTION system stored procedure. When turned on, the "TABLE LOCK ON BULK LOAD" option will force BCP to acquire a table lock. When turned off (which is the default), row level locking will be used. Row level locking will improve data availability to the users of your table; since each row is locked individually all the rest of the rows are available for reading and modification. With a table level lock, all the rest of the users will have to wait until you are done loading data into the table. Again, in general it is best to use BCP during off hours. You can specify a log file which will contain all data import / export errors as well as total number of errors that is acceptable to you. Once BCP encounters a higher number of errors other than specified by "-m" switch, it will unconditionally stop execution. Compared to DTS where you can write custom error handlers this is quite limited. BCP also lets you enable identity insert (also available with DTS), which lets the data import populate identity values. If this option is not used, SQL Server will assign identity values automatically. To use non-logged BCP, the SELECT INTO / BULKCOPY option has to be "on" for the database you wish to import / export data into or out of. With SQL Server 2000 this option can be set through the ALTER DATABASE statement or by using the system stored procedure SP_DBOPTION. To execute BCP you need to have a valid login account and appropriate read (SELECT) permissions on the data you export and write (INSERT) permissions for SQL Server table that you populate. In addition, only members of the fixed server roles SERVER ADMINISTRATORS, BULK INSERT ADMINISTRATORS and SYSTEM ADMINISTRATORS can execute BCP. BCP can be used in interactive or non-interactive (sometimes referred to as "fast") mode. In the interactive mode, the user provides the desired options one at a time, as prompted. One of the most useful options available with interactive BCP is creating a format file. A format file lets you specify a format in which you'd like to have data imported into or out of SQL Server once and then use it as many times as you wish. Format files created with the BCP utility can also be used with the BULK INSERT statement. Note that an output file does not have to exist before exporting data with BCP. However, a destination table must exist before being populated with BCP. |