bulk insert in the exact sequence as the text file

I'm having an issue when doing a bulk insert on a SQL 2000 server. I need
the table rows to load in the exact order that the rows in the text file are
sequenced.
So far, the bulk insert appears to be loading the file in 2 different
orders. One of the orders is the same order as the text file. The other
order is completely different and does not appear to have any reason to be i
n
the different order.
I've tried setting the BATCHSIZE argument to 1 and that doesn't fix it.
Using the ORDER argument doesn't help either because the destination table
doesn't have a primary key plus I don't want to change the order of the text
file anyways.
Is there a way to ensure the BULK INSERT loads the file in the exact order
that the text file is in?How do you know that table is not loaded in sequence from your text file? A
table is an unordered set of rows so you must specify ORDER BY when
selecting from the table in order to guarantee sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"eieed" <eieed@.discussions.microsoft.com> wrote in message
news:5A497B7A-E3EC-4C30-B1A4-D13C0E23DEA1@.microsoft.com...
> I'm having an issue when doing a bulk insert on a SQL 2000 server. I need
> the table rows to load in the exact order that the rows in the text file
> are
> sequenced.
> So far, the bulk insert appears to be loading the file in 2 different
> orders. One of the orders is the same order as the text file. The other
> order is completely different and does not appear to have any reason to be
> in
> the different order.
> I've tried setting the BATCHSIZE argument to 1 and that doesn't fix it.
> Using the ORDER argument doesn't help either because the destination table
> doesn't have a primary key plus I don't want to change the order of the
> text
> file anyways.
> Is there a way to ensure the BULK INSERT loads the file in the exact order
> that the text file is in?

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.