8. SQL Statements : COPY : Bulk Copying
 
Share this page                  
Bulk Copying
To improve performance when loading data from a file into a table, use a bulk copy. Bulk copy is possible for either formatted or unformatted copies. Certain WITH options are valid for bulk copy only, as described in the syntax.
The requirements for performing a bulk copy are:
The table is not journaled
The table has no secondary indexes
For storage structures other than heap, the table is empty and occupies fewer than 18 pages
If the DBMS Server determines that all these requirements are met, the data is loaded using bulk copy. If the requirements are not met, data is loaded using a less rapid technique. For detailed information about bulk copying, see the Database Administrator Guide.
To specify the estimated number of rows to be copied from a file to a table during a bulk copy operation, use WITH ROW_ESTIMATE (see ROW_ESTIMATE).
If you specify one of the WITH options that are valid only for bulk copy but the operation cannot be done as a bulk copy (for example, if the table is journaled), then the COPY statement will fail immediately. Using this method guarantees that the operation will either be a bulk copy or fail. This can save time and resources when loading millions of rows.