September 16, 2008 at 2:28 pm
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. SelburgSeptember 16, 2008 at 3:17 pm
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
September 16, 2008 at 3:24 pm
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. SelburgSeptember 17, 2008 at 3:29 am
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
September 17, 2008 at 10:19 am
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. SelburgSeptember 18, 2008 at 4:21 am
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.
September 19, 2008 at 9:28 am
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.
September 19, 2008 at 7:15 pm
Jason,
I have to ask... what's in these files where the order they are loaded in is important?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply