Openrowset with Excel - Will row order be maintained?

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

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

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.

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.