How to keep row order with SqlBulkCopy?

Question

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

Answer 1

If you can save the excel spreadsheet as a CSV it is very easy to generate a list of INSERT statements with any scripting language which will be executed in the exact same order as the spreadsheet. Here's a quick example in Groovy but any scripting language will do it just as easily if not easier:

def file1 = new File('c:\\temp\\yourSpreadsheet.csv')
def file2 = new File('c:\\temp\\yourInsertScript.sql')

def reader = new FileReader(file1)
def writer = new FileWriter(file2)

reader.transformLine(writer) { line ->
    fields =  line.split(',')

    text = """INSERT INTO table1 (col1, col2, col3) VALUES ('${fields[0]}', '${fields[1]}', '${fields[2]}');"""

}

You can then execute your "yourInsertScript.sql" against your database and your order will be the same as your spreadsheet.

Answer 2

I don't think that row ordering is specified or guaranteed by SQL unless you use an "ORDER BY " clause.

From a post by Bill Vaughn (http://betav.com/blog/billva/2008/08/sql_server_indexing_tips_and_t.html):

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:

http://sqlblogcasts.com/blogs/simons/archive/2007/08/21/What-is-the-position-of-a-row--.aspx

Answer 3

You might also be able to define an identity column in your table that auto-increments during data load. That way, you can sort on it later when you want the records in the same order again.