The bcp utility and BULK INSERT statement accept the ORDER hint and ORDER clause respectively, which allows the user to specify how data in the data file is sorted. Although it is not necessary for data in the data file to be sorted in the same order as the table, the same ordering can improve performance of the bulk copy operation.
The order of data in the table is determined by the clustered index. The order and columns listed in the ORDER hint or ORDER clause must match the columns, in the same order, in the clustered index to improve the performance of the bulk copy operation.
For example, to bulk copy data from the Authors.txt data file to the authors2 table in the pubs database, specifying that the data file is in ascending order on the au_id column, execute from the command prompt:
bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "ORDER (au_id ASC)"
Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:
BULK INSERT pubs..authors2 FROM 'c:\authors.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ORDER (au_id ASC)
)
By default, the bulk copy operation assumes that the data file is unordered.
| bcp Utility | Optimizing Bulk Copy Performance |
| BULK INSERT |