Maintain the sort order of data file while using bcp

Question

Maintain the sort order of data file while using bcp

Gil_Adi

SSCommitted

Points: 1669
More actions
August 14, 2013 at 7:05 am

#276160

Sql server 2008 r2 service pack 2
When trying to import a single varchar column file using bcp utility (no format file)
to a temporary table (## ) the rows are inserted not int the order of the file.
Doing the same thing with a regular table the order is maintaind
1. Is there a way to ensure that the file will be bulked insert with its order?
2. Is it a known Issue ?
:w00t:

Viewing 11 posts - 1 through 10 (of 10 total)

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Why do you need to keep a sort order during the insert? The sort order is only required during a display (SELECT) of the results and can be accomplished (and only guaranteed) with a ORDER BY statement.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

HanShi is exactly correct. My question is why you'd need to have inserted rows in a particular order. I can definitely see the case where you have time-sensitive data that you're importing and you want it to appear sequentially in the table, but the table storage order isn't defined like that.

If you do need to control the sequence in which your time-sensitive imported data is inserted into your table, then here's an approach you can use to accomplish this.

1. Import into a temporary table. Don't worry about the order.

2. Insert into your real table using a SELECT on your temporary table with an ORDER BY clause on your datetime fields.

3. Drop the temporary table.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

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

Ed Wagner (8/14/2013)
1. Import into a temporary table. Don't worry about the order.
2. Insert into your real table using a SELECT on your temporary table with an ORDER BY clause on your datetime fields.
3. Drop the temporary table.

Depending on the structure of the destination table, SQL may well completely ignore that order by. The only thing the order by is needed for is assigning an identity, as such if there's no identity there's no requirement to honour that order by and SQL can replace it with an order by to match the clustered index or leave it out entirely if the table is a heap.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Good point, Gail, thank you. I'd not even considered the possibility of a heap.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

good points Gail...

Thanks 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gil_Adi SSCommitted Points: 1669 More actions · Answer 6

Thanks Guys

The file being read contains Receipt Header with it corresponding lines just after the Receipt Raw header

The file includes several Receipts

So Maintianing the read order is very important

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

Gil_Adi (8/14/2013)
The file being read contains Receipt Header with it corresponding lines just after the Receipt Raw header
The file includes several Receipts
So Maintianing the read order is very important

A table, by definition, has no order. Hence any talk of maintaining insert order is meaningless. The rows in the table have no order, no matter how they were inserted.

Order is something that a result set has when queried, as a result of an ORDER BY specified on that select.

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

x SSC-Insane Points: 23660 More actions · Answer 8

Gil_Adi (8/14/2013)
Thanks Guys
The file being read contains Receipt Header with it corresponding lines just after the Receipt Raw header
The file includes several Receipts
So Maintianing the read order is very important

This stuff gets much easier if you can touch up the text file before importing, even if its as simple as prefixing a line number in each line. This way you have an explicit value that specifies the order that stays with the data once you import it.

I've also considered adding an identity column to the table I'm importing to but this leaves me wondering if this is too dependant on bcp not messing up the order of inserts.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 9

To meet your demands you first have to define a table with an identity column. Then you need to read the file line-by-line and add these as seperate records to the table. After the complete file is read, you can display the content of the table ordering by the identity column.

Because you will process the file line-by-line I guess you'll get the best performance by using a client application (programming language like C#).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Gil_Adi (8/14/2013)
Sql server 2008 r2 service pack 2
When trying to import a single varchar column file using bcp utility (no format file)
to a temporary table (## ) the rows are inserted not int the order of the file.
Doing the same thing with a regular table the order is maintaind
1. Is there a way to ensure that the file will be bulked insert with its order?
2. Is it a known Issue ?
:w00t:

1. What is the CREATE TABLE syntax for your Temp Table?

2. How many rows are in the file?

3. Can you post the first 10 rows of the file?

~~4. Why is it important to maintain the same order in the table as what's in the file? (For curiosity sake)~~

Cancel the 4th question. I found the answer in a previous post on this thread.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)