Import File to Table without changing the order

Question

Import File to Table without changing the order

Kranthi Babu

Ten Centuries

Points: 1094
More actions
February 27, 2011 at 11:23 pm

#143419

I'm uploading a file using Data Flow Task to a SQL Table. I observed that data in the destination is not in the same order as in the File. Is it a general behaviour? If so, is there is any possibility to make the data available in the same order as in the file??
Note : I'm using the default settings of OLEDB destination. Didnot specify any rows per batch. Max.Insert size is 2147483647- default value.
Im using the Table-Fast Load option.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I'm curious how you are verifying insert order.. For a select statement in sql 2005+ (and perhaps earlier) you could not guarantee the order that rows were returned without an ORDER BY statement.. So before trying to evaluate if there is something to fix can you answer that first question..

CEWII

Kranthi Babu Ten Centuries Points: 1094 More actions · Answer 2

there was a view which is written as "select top 1 cast(... as datetime)...", which is querying on the table for which data is inserted with file.

When i actually looked into the file which was used to upload the data, the first row is a date. But when the file data is uploaded, first row is not the same row as of file.

I mean first row of table as "select top 1 columnname from table1".

I understand that order will not be the same when uploaded. Is there any way i can retain the order.?

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 3

Kranthi Babu (2/28/2011)
there was a view which is written as "select top 1 cast(... as datetime)...", which is querying on the table for which data is inserted with file.
When i actually looked into the file which was used to upload the data, the first row is a date. But when the file data is uploaded, first row is not the same row as of file.
I mean first row of table as "select top 1 columnname from table1".
I understand that order will not be the same when uploaded. Is there any way i can retain the order.?

The only way you can be sure of an order is to use the ORDER BY clause.

By definition in the relation theory, a table does not keep some sort of order.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ken McKelvey SSCoach Points: 18876 More actions · Answer 4

To confirm what has already been said, a relation (table view etc) is an unordered set.

If you are importing data from a text file, and the original order is important, you will need to add an additional column, say LineNumber, to the text file. When the text file is then imported you will then be able to get the original order by doing thing like:

SELECT *

FROM ImportTable

ORDER BY LineNumber