Export (0) Print
Expand All

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.

Community Additions

ADD
Show:
© 2014 Microsoft