如何使用SqlBulkCopy保持行顺序?

excel export import sqlbulkcopy sql-server

我正在使用SqlBulkCopy以编程方式将数据从Excel导出到SQL Server 2005。它工作得很好,我唯一的问题是它不保留我在Excel文件中的行序列。我没有要排序的列,我只想按照它们在Excel电子表格中显示的顺序插入记录。

我无法修改Excel文件,必须使用我所拥有的。按任何现有列排序将破坏序列。

请帮忙。

PS完成向电子表格插入ID列,看起来在导出/导入期间无法保留订单

一般承认的答案

我不认为SQL指定或保证行排序,除非您使用“ORDER BY”子句。

来自Bill Vaughn的帖子( http://betav.com/blog/billva/2008/08/sql_server_indexing_tips_and_t.html ):

使用Order By:即使表具有聚簇索引(以物理顺序存储数据),SQL Server也不保证以该(或任何特定)顺序返回行,除非使用ORDER BY子句。

另一个信息链接:

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


热门答案

经过大量研究后,似乎很明显没有办法保留行顺序,因为它是Microsoft编写的Bulk Insert命令。您必须自己直接将ID列添加到导入文件中,使用shell或其他外部脚本,或者不使用。对于微软来说,这似乎是一个需要(和简单)的功能,但是经过十多年的努力,它不会发生。

然而,我需要在导入后保留导入文件中的实际记录顺序,因为如果设置列具有相同的值,则更高的记录将取代较低的记录。

所以我走了另一条路。我的约束是:

  • 我根本无法更改源文件。 (并开创了一个不好的先例!)
  • 我无法使用外部脚本。太复杂。它必须是一个简单的基于T-Sql的解决方案,没有CMD执行。这需要进入单个程序,因此可以实现自动化。

我喜欢使用Powershell为每一行创建有序插入语句,然后在Sql中运行的逻辑。它基本上是为每个记录排队,而不是BULK插入。是的,它会起作用,但也会很慢。我经常有500K +行的文件。我需要快速的东西。

所以我遇到了XML。批量上传文件直接上传到单个XML变量中。这将保留记录的顺序,因为每个记录都添加到XML中。然后解析XML变量并将结果插入表中,同时添加标识列。

假设导入文件是标准文本文件,每个记录以换行符结尾(Char(13)+ Char(10))

我的方法有两个步骤:

  1. 执行IMPORT SQL语句(使用OPENROWSET),使用XML标记封装每个记录。将结果捕获到XML变量中。

  2. 通过XML标记将变量解析为表,添加递增[ID]列。

    ---------------------------------
    Declare @X xml;
    ---------------------------------
    SELECT @X=Cast('<X>'+Replace([BulkColumn],Char(13)+Char(10),'</X><X>')+'</X>' as XML)
    FROM OPENROWSET (BULK N'\\FileServer\ImportFolder\ImportFile_20170120.csv',SINGLE_CLOB) T
    ---------------------------------
    SELECT [Record].[X].query('.').value('.','varchar(max)') [Record]
    ,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) [ID]
    --Into #TEMP 
    FROM @X.nodes('X') [Record](X);
    ---------------------------------
    
    • XML标记替换每个换行符。

    • 如果文件以换行符结尾,则会在末尾添加空白行。只需删除最后一行。

我使用动态sql将其写入我的程序中,因此我可以传入FileName并将ID设置为从1或0开始(如果有标题行)。

我能够在大约5秒内对300K记录的文件运行它。




许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因