如何使用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
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow