用BULK INSERT命令匯入資料詳解

       如果你從事與資料庫相關的工作，有可能會涉及到將資料從外部資料檔案插入倒SQL Server的操作。本文將為大家示範如何利用BULK INSERT命令來匯入資料，並講解怎樣通過改變該命令的一些選項以便更方便且更有效地插入資料。

　　BULK INSERT

　　在SQL Server中，BULK INSERT是用來將外部檔案以一種特定的格式載入到資料庫表的T-SQL命令。該命令使開發人員能夠直接將資料載入到資料庫表中，而不需要使用類似於Integration Services這樣的外部程式。雖然BULK INSERT不允許包含任何複雜的邏輯或轉換，但能夠提供與格式化相關的選項，並告訴我們匯入是如何?的。BULK INSERT有一個使用限制，就是只能將資料匯入SQL Server。

　　插入資料下面的例子能讓我們更好的理解如何使用BULK INSERT命令。首先，我們來建立一個名為Sales的表，我們將要把來自文字檔的資料插入到這個表中。

　　CREATE TABLE [dbo].[Sales] 
　　( 
　　[SaleID] [int], 
　　[Product] [varchar](10) NULL, 
　　[SaleDate] [datetime] NULL, 
　　[SalePrice] [money] NULL 
　　) 

    當我們使用BULK INSERT命令來插入資料時，不要啟動目標表中的觸發器，因為觸發器會減緩資料匯入的進程。

　　在下一個例子中，我們將在Sales表上建立觸發器，用來列印插入到表中的記錄的數量。

　　CREATE TRIGGER tr_Sales 
　　ON Sales 
　　FOR INSERT 
　　AS 
　　BEGIN 
　　PRINT CAST(@@ROWCOUNT AS VARCHAR(5)) + ' rows Inserted.' 
　　END 

    這裡我們選擇文字檔作為來源資料檔案，文字檔中的值通過逗號分割開。該檔案包含1000條記錄，而且其欄位和Sales表的欄位直接關聯。由於該文字檔中的值是由逗號分割開的，我們只需要指定FIELDTERMINATOR即可。注意，當下面這條語句運行時，我們剛剛建立的觸發器並沒有啟動：

　　BULK INSERT Sales FROM 'c:SalesText.txt' WITH (FIELDTERMINATOR = ',')

　　當我們要的資料量非常大時，有時候就需要啟動觸發器。下面的指令碼使用了FIRE_TRIGGERS選項來指明在目標表上的任何觸發器都應當啟動：

　　BULK INSERT Sales FROM 'c:SalesText.txt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS)

　　我們可以使用BATCHSIZE指令來設定在單個事務中可以插入到表中的記錄的數量。在前一個例子中，所有的1000條記錄都在同一個事務中被插入到目標表裡。下面的例子，我們將BATCHSIZE參數設定為2，也就是說要對該表執行500次獨立的插入事務。這也意味著啟動500次觸發器，所以將有500咯列印指令輸出到螢幕上。
BULK INSERT Sales FROM 'c:SalesText.txt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS, BATCHSIZE = 2)

　　BULK INSERT不僅僅可以應用於SQL Server 2005的本機對應磁碟機。下面的語句將告訴我們如何從名為FileServer的伺服器的D盤中將SalesText檔案的資料匯入。

　　BULK INSERT Sales FROM 'FileServerD$SalesText.txt' WITH (FIELDTERMINATOR = ',')

　　有時候，我們在執行匯入操作以前，最好能先查看一下將要輸入的資料。下面的語句在使用BULK命令時，使用了OPENROWSET函數，以便從SalesText文字檔中讀取來源資料。該語句同時還需要使用一個格式檔案(此處沒有列出檔案的具體內容)來表明該文字檔中的資料格式。

　　SELECT * 
　　FROM OPENROWSET(BULK 'c:SalesText.txt' , 
　　FORMATFILE='C:SalesFormat.Xml' 
　　) AS mytable; 
　　GO 

      

最近做某項目的資料庫分析，要實現對海量資料的匯入問題，就是最多把200萬條資料一次匯入sqlserver中，如果使用普通的insert語句進行寫出的話，恐怕沒個把小時完不成任務，先是考慮使用bcp，但這是基於命令列的，對使用者來說友好性太差，實際不大可能使用；最後決定使用BULK INSERT語句實現，BULK INSERT也可以實現大資料量的匯入，而且可以通過編程實現，介面可以做的非常友好，它的速度也很高：匯入100萬條資料不到20秒中，在速度上恐怕無出其右者。
但是使用這種方式也有它的幾個缺點：
1.需要獨佔接受資料的表
2.會產生大量的日誌
3.從中取資料的檔案有格式限制
但相對於它的速度來說，這些缺點都是可以克服的，而且你如果願意犧牲一點速度的話，還可以做更精確的控制，甚至可以控制每一行的插入。
對與產生佔用大量空間的日誌的情況，我們可以採取在匯入前動態更改資料庫的日誌方式為大容量日誌記錄復原模式，這樣就不會記錄日誌了，匯入結束後再恢複原來的資料庫日誌記錄方式。
具體的一個語句我們可以這樣寫：

代碼如下:

alter database taxi
set RECOVERY BULK_LOGGED
BULK INSERT taxi..detail FROM 'e:\out.txt'

WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
TABLOCK
)
alter database taxi
set RECOVERY FULL

這個語句將從e:\out.txt匯出資料檔案到資料庫taxi的detail表中。