I'm exporting data programatically from Excel to SQL Server 2005 using SqlBulkCopy. It works great, the only problem I have is that it doesn't preserve the row sequence i have in Excel file. I don't have a column to order by, I just want the records to be inserted in the same order they appear in the Excel Spreadsheet.
I can't modify the Excel file, and have to work with what I've got. Sorting by any of the existing columns will break the sequence.
Please help.
P.S. Ended up inserting ID column to the spreadsheet, looks like there's no way to keep the order during export/import
解决方案
I don't think that row ordering is specified or guaranteed by SQL unless you use an "ORDER BY " clause.
Using Order By: Even when a table has
a clustered index (which stores the
data in physical order), SQL Server
does not guarantee that rows will be
returned in that (or any particular)
order unless an ORDER BY clause is
used.
Another link with info: