如何使用SqlBulkCopy保持行顺序？ [英] How to keep row order with SqlBulkCopy?

问题描述

我正在使用SqlBulkCopy将数据从Excel编程导出到SQL Server 2005。它的功能非常好，唯一的问题是它不保留我在Excel文件中的行序列。我没有一个列来排序，我只是希望以与Excel电子表格中相同的顺序插入记录。

我无法修改Excel文件，并且必须使用我所拥有的。对任何现有列进行排序将会打破序列。

请帮助。

将ID列插入电子表格，看起来没有办法在导出/导入期间保持顺序

解决方案

我不t认为排序由SQL指定或保证，除非您使用ORDER BY子句。

从Bill Vaughn的一篇文章（ http://betav.com/blog/billva/2008/08/sql_server_indexing_tips_and_t.html ）：

使用排序方式：即使表中有
a聚簇索引（以物理顺序存储
数据） ，SQL Server
不保证行
返回（或任何特定的）
，除非ORDER BY子句是
。

另一个链接信息：

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

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.

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

这篇关于如何使用SqlBulkCopy保持行顺序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！