To bulk copy data as fast as possible, several options are available to specify how data should be bulk copied into Microsoft® SQL Server™ using the bcp utility or BULK INSERT statement, including:
For more information, see Factors Affecting Bulk Copy Performance.
Note If possible, use the BULK INSERT statement rather than the bcp utility to bulk copy data into SQL Server. The BULK INSERT statement is faster than the bcp utility.
Two factors determine which of these options can or should be used to increase the performance of bulk-copy operations:
Additionally, these factors depend on whether data is bulk copied into a table from a single client or in parallel from multiple clients.
When loading data into an empty table from a single client, it is recommended that you specify:
Additionally, if your table has a clustered index and the data in the data file is ordered to match the clustered index key columns, bulk copy the data into the table with the clustered index already in place and specify the ORDER hint. This is significantly faster than creating the clustered index after the data is copied into the table.
If nonclustered indexes are also present on the table, drop these before copying data into the table. It is generally faster to bulk copy data into a table without nonclustered indexes, and then to re-create the nonclustered indexes, rather than bulk copy data into a table with the nonclustered indexes in place.
When copying data into a table that has existing data, the recommendation to perform the bulk copy operation with the indexes in place depends on the amount of data to be copied into the table compared to the amount of existing data already in the table. As the percentage of data to be copied into the table increases (based on the amount of existing data in the table), the faster it is to drop all indexes on the table, perform the bulk copy operation, and then re-create the indexes after the data is loaded.
As a rough guide, here are suggested figures for the amount of data to be added to a table for various types of indexes. If you exceed these percentages, you may find it faster to drop and re-create the indexes.
| Indexes | Amount of data added |
|---|---|
| Clustered index only | 30% |
| Clustered and one nonclustered index | 25% |
| Clustered and two nonclustered indexes | 25% |
| Single nonclustered index only | 100% |
| Two nonclustered indexes | 60% |
If SQL Server is running on a computer with more than one processor and the data to be bulk copied into the table can be partitioned into separate data files, then it is recommended that data be loaded into the same table from multiple clients in parallel, thereby improving the performance of the bulk-copy operation.
When copying data into a table from multiple clients, consider that:
As with bulk-copy operations from a single client, specify:
If data is being copied from one computer running SQL Server to another, perform all bulk-copy operations using either native or Unicode native format. For more information, see Native, Character, and Unicode Formats.
If the source table has a clustered index or if you intend to bulk copy the data into a table with a clustered index:
| Copying Data Using bcp or BULK INSERT | SQL Server: Databases Object |