Using Parallel Bulk Copy to Copy Data into a Specific Partition

Use parallel bulk copy to copy data in parallel to a specific partition. Parallel bulk copy substantially increases performance during bcp sessions because it can split large bulk copy jobs into multiple sessions and run the sessions concurrently.

To use parallel bulk copy:

The destination table must be partitioned. Use:
- sp_helpartition – to see the number of partitions on the table.
- alter table ... partition – to partition the table, if the table is not already partitioned.
The destination table should not contain indexes because:
- If the table has a clustered index, this index determines the physical placement of the data, causing the partition specification in the bcp command to be ignored.
- If any indexes exist, bcp automatically uses its slow bulk copy instead of its fast bulk copy mode.
If nonclustered indexes exist on the tables, parallel bulk copy is likely to lead to deadlocks on index pages.
Each partition should reside on a separate physical disk for the best performance.
Before you copy data into your database, partition the table destined to contain the data.
Parallel bulk copy can copy in to a table from multiple operating system files.
- For all types of partitioned tables, use:
```
bcp tablename partition partition_name in file_name
```
- For round-robin partitioned tables only, use:
```
bcp tablename partition_number in file_name
```

Copying data into a round-robin partitioned table using parallel bulk copy
Image shows how data is tracked when you copy data into a round-robin partitioned table. Starting on the first partition, the data is copied through the partitions til it reaches the last partition in the table.

Image shows how data is tracked when you copy data into a round-robin partitioned table. Starting on the first partition, the data is copied through the partitions til it reaches the last partition in the table.

See the Transact-SQL Users Guide for information about partitioning a table.

Note: When using parallel bulk copy to copy data out, you cannot specify a partition number. You can, however, specify a partition name.

bcp in and Locks
When you copy in to a table using bcp—particularly when you copy in to a table using parallel bcp—the copy process acquires an exclusive lock.
Parallel Bulk Copy Methods
There are various methods to copy in data using parallel bulk copy.
Parallel Bulk Copy Syntax
Use this syntax for parallel bulk copy, when using several simultaneous sessions.
Using Parallel Bulk Copy on Round-robin Partitioned Tables
Copy sorted data in parallel into a specific partition.
Parallel Bulk Copy and IDENTITY Columns
When you use parallel bulk copy, IDENTITY columns can cause a bottleneck.

Parent topic: Improve bcp Performance