• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

Openrowset with Excel - Will row order be maintained?

I'm reading an Excel worksheet into SQL through Openrowset.  I need to maintain the sequence of records from the spreadsheet into the database.  I can do this easily through an identity field, but the question is - will openrowset always return the rows in the same order as the are in the spreadsheet?
0
Pentegra
Asked:
Pentegra
  • 3
  • 2
1 Solution
 
liorfrCommented:
You can specify the column that you would like to order by.
In your excel add an incremental ID column and specify it in the Openrowset Order argument.

http://technet.microsoft.com/en-us/library/ms190312.aspx

Lior
0
 
PentegraAuthor Commented:
Thanks for responding.  To clarify, I need to retain whatever order the rows are in without adding any columns to the worksheet.  The users put the data they want to import at the top of the worksheet, then add blank rows and put the data they don't want to import below that.  I need to only pick up the records before the first blank row.  I insert into a staging table which has an identity column in it.  I select the min(idnum) where <field> is null and then delete records with a higher idnum.  What I need to confirm is that the rows are always read from top to bottom in the worksheet.

Rob
0
 
liorfrCommented:
A table is an unordered set. Order is guaranteed only when the ORDER BY clause is used in the outer query and only in retrieval operations. If ORDER BY is not used the Optimizer is free to insert the rows into the table regardless of the way they have been ordered at the source.

If you want to preserve a specific row order, you need to consider that at design time. You could, for instance, use an additional column to store the values you want to order the rows by - SQL Server cannot do that for you.
0
 
PentegraAuthor Commented:
I agree that the Optimizer is free to choose how to present rows when a select is done with no order by clause.  I don't believe that the Optimizer would change insert order though.   It has to be inserting records in the order that they are passed to the insert statement.   This leads back to the issue being the order in which the rows are presented to the insert statement.

In BOL (http://technet.microsoft.com/en-us/library/ms190312.aspx)  it states that regarding the query in the openrowset: "The local instance of SQL Server does not process this query, but processes query results returned by the provider, a pass-through query. " 

The issue then is, does the OLE provider (which I don't believe has an optimizer) present the records in the same order as they are read in from the source?

Note, I've been running tests for a few days and each time it retains the order, but this is not definitive proof that it could be otherwise.
0
 
liorfrCommented:
In the documentation it is stated that OPENROWSET with the BULK option may be loaded by parallel streams therefore the order may differ.
I think it is safe to say that the order does not change if there is only one stream.

i wouldn't call it 100% but i think you can go forward with that
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now