SQLServer大資料量插入BULK INSERT

SQLSERVER 大資料量插入命令：

BULK INSERT是SQLSERVER中提供的一條大資料量匯入的命令，它運用DTS(SSIS)匯入原理，可以從本地或遠程伺服器上大量匯入資料庫或檔案資料。批量插入是一個獨立的操作，優點是效率非常高。缺點是出現問題後不可以復原。

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

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

本機資料操作：

 當我們使用BULK INSERT命令來插入資料時，建立的表有無觸發器需要不同的配置，我們分別舉例來看：

【A沒有觸發器的表】

　　CREATE TABLE [dbo].[Sales] 

　　( 

　　 [SaleID] [int], 

　　 [Product] [varchar](10) NULL, 

　　 [SaleDate] [datetime] NULL, 

　　 [SalePrice] [money] NULL 

　　) 

    【B有觸發器的表】

　　還是在上面表的基礎上，我們建立觸發器，用來列印插入到表中的記錄的數量。

　　CREATE TRIGGER tr_Sales 

　　ON Sales 

　　FOR INSERT 

　　AS 

　　BEGIN 

　　PRINT CAST(@@ROWCOUNT AS VARCHAR(5)) + ' rows Inserted.' 

　　END 

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

A：沒有觸發器的操作

BULK INSERT Sales FROM 'C:\Users\Administrator\Desktop\Sales.txt' WITH (FIELDTERMINATOR = ',')

B：有觸發器的操作

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

　　BULK INSERT Sales FROM 'C:\Users\Administrator\Desktop\Sales.txt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS)

　　C：舉一反三

我們可以使用BATCHSIZE指令來設定在單個事務中可以插入到表中的記錄的數量。假如檔案中共有1000條記錄。在前一個例子中，所有的1000條記錄都在同一個事務中被插入到目標表裡。下面的例子，我們將BATCHSIZE參數設定為2，也就是說要對該表執行500次獨立的插入事務。這也意味著啟動500次觸發器，所以將有500列印指令輸出到螢幕上。

BULK INSERT Sales FROM 'c:\Salestxt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS, BATCHSIZE = 2)

遠端資料操作：

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

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

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

　　SELECT * 

　　FROM OPENROWSET(BULK 'c:SalesText.txt' , 

　　FORMATFILE='C:\SalesFormat.Xml' 

　　) AS mytable; 

GO