Does OPENROWSET using BULK preserve the original file's order?

  • Does OPENROWSET using BULK preserve the original file's order

    OR

    Will the rows be inserted/opened in the same order that they exist in the file?

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Bulk will ...

    But you recipient table may have a clustering index, needing to reorder the rows.

    Will sqlserver retrieve in the same order than you've put it in the table.

    Most certainly not.... If you want sorted output, you need to specify an "order by" clause in your final select.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I have a PK on the table, and the original file's line number is all that is important. That is, I'm not concerned how it is stored in the table, just that the line numbers are correct.

    I'm going to create a few sample files to verify this. It's not that I don't believe you, but in everything i've read, I can't find an absolute answer either way via microsoft.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg (9/16/2008)


    I have a PK on the table, and the original file's line number is all that is important. That is, I'm not concerned how it is stored in the table, just that the line numbers are correct..

    - If the line number is a column value, you should be OK.

    - keep in mind, in a set there is no order, unless you define one in your final query.

    ... It's not that I don't believe you, ....

    - It's already a long time ago since IT was an exact science 😉

    - you would better doubt me ..... I can only propose what worked for me ...:hehe:

    - Best is to always test the proposed solutions !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • The problem is that the line number is NOT a column in the source file.

    I tested with a file of 10 million rows and it worked fine, but that's not enough for me to just say it works 100% of the time.

    I guess I'm screwed until I find some REAL documentation. :w00t:

    I just find it hard to believe that a OPENROWSET (BULK ...) command wouldn't, or doesn't have an option to retain the original file order. I've had one person tell me that they've had it not do that for them, which is why I posted here.

    I guess I'll email MS and wait for an answer. I'll then post their reply here.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • I learned a while back to never depend on records being delivered in file order. The AS/400 wouuld do that (called arrival order), but for everything else, I use an order by field, even if it's artificially derived.

  • Jason, might I suggest that you look at EXCEL's help file. In particular the subject Automatically numbering rows. An extract of what can be done is:

    Select the first cell in the range that you want to fill.

    Type the starting value for the series.

    Type a value in the next cell to establish a pattern.

    How?

    For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4.

    Select the cells that contain the starting values.

    Drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) across the range that you want to fill.

    To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left.

    . The task

    Agreed for an existing 10 million row file just the dragging would be a long time chore. But for a new EXCEL worksheet if you could get the author(s) of the sheet to do the task it might not be as onerous.

    In your case the numbered column would be an additional column on the work sheet which is the included in the bulk import and your DB table would of course have a column for this additional value.

    I have not investigated wether the numbering task could also be performed by a macro in the Excel workbook, but that might be worth while checking.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Jason,

    I have to ask... what's in these files where the order they are loaded in is important?

    --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)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 7 (of 7 total)

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