The process appears to hang because, although it has sent all the data to SQL Server, the data has only been placed in sort buffers - it has not yet reached the destination table.
When the table has indexes, SQL Server will sort the data into the required index order before inserting. If the data set is large, there are many indexes, or SQL Server has insufficient sort memory available, this process can take significant time. The presence of indexes and existing table data can also affect the ability of SQL Server to use optimized minimally-logged inserts.
Without minimally-logged inserts, the insertion process (after sorting) will also be slow because each row is fully recorded in the transaction log (including the information needed to undo the insert to ensure recoverability).
Several bcp options can be specified to help achieve minimally-logged inserts. Other measures may also be necessary, such as temporarily changing the recovery model of the database to support efficient bulk operations. Dropping nonclustered indexes before the insert and rebuilding them afterwards is also often the optimal strategy.
To summarize the main points of a complex topic:
- Use a recovery model that supports minimally-logged inserts
- Specify a
TABLOCK hint (exact syntax varies depending on insert method)
- Specify the
ORDER hint and ensure the data source is pre-sorted by the clustering key
- Disable triggers and constraints
- Load into an empty table if possible
For more information, see:
Optimizing Bulk Insert Performance and linked pages
For SQL Server 2008 and later:
The Data Loading Performance Guide