Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Controlling the Sort Order When Bulk Importing Data

By default, a bulk-import operation assumes that a data file is unordered. If the table has a clustered index, the bcp command, BULK INSERT statement (Transact-SQL), and OPENROWSET(BULK…) function (Transact-SQL) let you specify how data in the data file is sorted during a bulk-import operation. Sorting data in the data file in the same order as the table is optional. However, specifying the same sort order can increase the speed of the bulk-import operation.

The following table summarizes the command qualifiers for specifying the sort order for a bulk-import operation.

Command

Qualifier

Qualifier type

bcp

-h"ORDER(column [ASC

DESC] [,...n] )"

Hint

BULK INSERT

ORDER ( { column [ ASC

DESC ] } [ ,...n ] ) ]

Argument

OPENROWSET(BULK…)

ORDER ( { column [ ASC | DESC ] } [ ,...n ] [ UNIQUE ] )

Hint

Best Practice   The order of data in the table is determined by the clustered index. To improve the bulk-import performance when a table has a clustered index, the columns listed in the ORDER hint or ORDER clause should match the columns and be in the same order as in the clustered index.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.