Openrowset with Excel  - Will row order be maintained?

Posted on 2010-01-06
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
    LVL 4

    Expert Comment

    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

    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.

    LVL 4

    Expert Comment

    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

    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 (  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.
    LVL 4

    Accepted Solution

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now