BCP - getting data in the same order of data file

Question

Post reply

BCP - getting data in the same order of data file

renatomachado

Grasshopper

Points: 13
More actions
October 5, 2005 at 4:29 pm

#169128

I´m trying to get data from a large data file (.txt) into a table using BCP or BULK INSERT, but i need the rows on the table to be in the same order of the rows of the data file. Someone knows how can i do to BCP save the rows on the table in the same order they are on the data file?
I´m using SQL SERVER 2000 SP3. The table does not have any index, primary key, constraint or relationship with another table.
The BCP command i´m using: BCP DBSBA..TMP_ARQUIVO IN \\SRV22\D$\TEMP_DBA\teste7.txt -C ACP -c -SSRV14B -Umyuser -Ppassword

Thanks.
Renato.

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

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

ziggy starlight Old Hand Points: 352 More actions · Answer 1

Try this one:

1) create a primary key - integer, identitiy

2) run bcp or Bulk Insert statement

This should help if BCP does not reorder the data before importing, which I doubt.

***

SDM SSCommitted Points: 1581 More actions · Answer 2

The problem seems to be more about expectation of retrieval order, rather than the insert order.

AFAIK bcp / BULK INSERT will deal with the data in the order dictated by the data file. The requirement "I need the rows in the table to be in the same order as they are on the data file" can only be satisfied if there's a clustered index based on an ordering key from the file, but that's usually kinda irrelevant - if SELECT queries don't explicitly specify an ORDER BY clause you have no expectation of a stable retrieval order.

Suggest you do as ziggy has suggested, AND ensure you always specify an order.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

According to relational theory, the phrase 'order of rows in a table' is meaningless.

The fact that, in SQL, a clustered index gives the data a natural sort order is more an accident of physical implementation than anything else and should not be relied upon.

If you need data retrieved in a certain order, add an ORDER BY clause to the SELECT.

Why do you want the data in the table in a certain order?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass