使用BULK INSERT高效匯入大量資料到SQL Server資料庫

來源資料 (文字檔)

        下載了大量的股票曆史資料, 都是文字格式設定的:

        每個檔案第一行包含股票代號, 股票名稱, 資料類型. 第二行是資料列的名稱:

資料表

        在資料庫中建立了一個資料表TestStock, 並設定以下欄位, 但沒有關於"成交額"的欄位, 因為以後的計算不會用到這個資料. 另外這裡關於價格的欄位沒有使用money資料類型, decimal足矣.

編寫格式檔案

        編寫格式檔案請參考:

1. XML 格式檔案的架構文法

2. XML 格式檔案樣本

        當前資料的格式檔案為:

<?xml version="1.0"?><BCPFORMATxmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  <RECORD>    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," />    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," />    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," />    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," />    <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," />    <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," />    <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\r\n" />  </RECORD>  <ROW>    <COLUMN SOURCE="1" NAME="Date" xsi:type="SQLDATE"/>    <COLUMN SOURCE="2" NAME="OpenPrice" xsi:type="SQLDECIMAL" PRECISION="6" SCALE="2" />    <COLUMN SOURCE="3" NAME="HighPrice" xsi:type="SQLDECIMAL" PRECISION="6" SCALE="2" />    <COLUMN SOURCE="4" NAME="LowPrice" xsi:type="SQLDECIMAL" PRECISION="6" SCALE="2" />    <COLUMN SOURCE="5" NAME="ClosePrice" xsi:type="SQLDECIMAL" PRECISION="6" SCALE="2" />    <COLUMN SOURCE="6" NAME="Volumn" xsi:type="SQLINT"/>  </ROW></BCPFORMAT>

        暫且先儲存在C盤目錄下吧, 檔案名稱叫BCPFORMAT.xml.

編寫BULK INSERT語句

        關於BULK INSERT的文法請參考這篇文檔, 這裡用到的參數主要為FORMATFILE, FIELDTERMINATOR和ROWTERMINATOR.

BULK INSERT TestStock

     FROM 'C:\SH600475.txt'

     WITH (

             FORMATFILE = 'C:\BCPFORMAT.xml',

             FIELDTERMINATOR = ',',

             ROWTERMINATOR = '\r\n' )

         本來也應該用到 FIRSTROW 這個屬性的, 因為我想跳過文本的前兩行, 畢竟第三行開始才是真正的資料. 但我遇到了和這個文章一樣的問題, 就是設定 FIRSTROW 為3的時候, 實際上卻從文本第5行的資料開始錄入, 當我設定 FIRSTROW 為1(本想這次應該是從第文本3行開始錄入吧), 結果報錯, 說一行資料格式有問題, 後來我處理了一下資料: 在BULK INSERT語句執行前, 先刪除文本的前兩行, 並且在BULK INSERT語句中不指定 FIRSTROW 屬性, 希望有人能告訴我這邊最好應該怎麼做...

執行

        BULK INSERT的速度很快, 我這個例子幾乎就不花時間, 而且達到了我想要的效果:

        同樣的效果, 如果從文本中讀一行記錄, 執行一次 INSERT INTO 語句的話, 需要10秒左右, 由此可見 BULK INSERT的高效.

參考

        1. http://msdn.microsoft.com/zh-cn/library/ms188365.aspx

        2. http://msdn.microsoft.com/zh-cn/library/ms189327.aspx

        3. http://msdn.microsoft.com/zh-cn/library/ms191234.aspx

        4. http://stackoverflow.com/questions/1029384/sql-bulk-insert-with-firstrow-parameter-skips-the-following-line

本文連結: http://www.cnblogs.com/technology/archive/2011/08/10/2133734.html