Openrowset with Excel  - Will row order be maintained?

Posted on 2010-01-06
Medium Priority
Last Modified: 2012-05-08
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?
Question by:Pentegra
  • 3
  • 2

Expert Comment

ID: 26195266
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.



Author Comment

ID: 26200501
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.


Expert Comment

ID: 26201301
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.

Author Comment

ID: 26202466
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.

Accepted Solution

liorfr earned 500 total points
ID: 26202959
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question