BULK INSERT - insert sequence

Hello,

I am importing some tab-delimited text files into a Table. I would like to import the rows in the same sequence as displayed in the text file. BULK INSERT will do this sometimes and not other times.

Here are my test commands:

Code Snippet

deletefrom SampleReport

go

BULKINSERT SampleReport FROM'C:\Sample.tab'

go

Select*from SampleReport

go

As I run these commands over-and-over I notice that the insert is the same as the source file on an alternating basis.

Is this normal? Is there a way to have it Insert in the same sequence every time?

Thanks

Tom

Your query doesn't use any order by cluase. So you can't ensure wheater it is ordered properly are not.

The order sequence may change when the clustered indexed used in your table.

To ensure the order you can use the Identity column.

Here the sample,

Code Snippet

Create table #Data

(

Id int primary Key,

OrderSeq Int Identity(1,1)

)

Insert Into #Data Values (3)

Insert Into #Data Values (1)

Insert Into #Data Values (2)

Insert Into #Data Values (5)

Insert Into #Data Values (4)

--In Your Case here it is BULK INSERT

Select* from #Data Order By OrderSeq --Your Expected Order

Select* from #Data --Unexpected Order, using the Clustered Index

Drop Table #Data

|||

A table, by definition, is an unordered set of rows. There is no such thing as inserting and storing the rows in any given order. The only way you can get rows back in the order you desire is by using an ORDER BY clause on your select statement.

It may seem at times that you "always" get rows back in order of a clustered index when you do a select statement without an ORDER BY clause. however, it is never guaranteed so you should not depend on it.

|||

Thank you for taking time to answer. Too bad we cannot just add rows and get them back in the same order without adding a key.

|||

If you have a clustered index on your destination table and it's the same ordering as the input data, then you can add the ORDER hint to BULK INSERT to possibly improve performance on the load.

FROM BOL:

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

Specifies how the data in the data file is sorted. Bulk load operation performance is improved if the data loaded is sorted according to the clustered index on the table. If the data file is sorted in a different order, or there is no clustered index on the table, the ORDER clause is ignored. The column names supplied must be valid columns in the destination table. By default, the bulk insert operation assumes the data file is unordered.