積沙成塔

【情境說明】

論壇上有朋友提到使用 BULK INSERT 匯入文字檔到 SQL Server 時，發生錯誤時為什麼不是整個匯入作業都回復？本文就針對此一情境來進行實驗。

【前置作業】

首先使用下列 T-SQL 建立測試資料表：

   1: USE tempdb

   2: GO

   3:  

   4: IF OBJECT_ID('dbo.Person') IS NOT NULL 

   5:     DROP TABLE dbo.Person

   6: GO

   7:  

   8: CREATE TABLE [dbo].[Person]

   9: (

  10:  [FirstName] [varchar](50) NULL,

  11:  [LastName] [varchar](50) NULL,

  12:  [EmailAddress] [varchar](16) NULL

  13:  )

  14:  

  15:  GO

 

接著筆者在 c:\temp 下建立一個名稱為  test.txt 的文字檔，內容如下：

【實作步驟】

然後使用 BULK INSERT 匯入資料，程式碼如下：

   1: USE tempdb

   2: GO

   3:  

   4: IF EXISTS (SELECT * FROM dbo.Person)

   5:     DELETE FROM dbo.Person

   6: GO

   7:  

   8: BULK INSERT dbo.Person FROM 'c:\temp\test.txt' WITH (FIELDTERMINATOR = ',')

   9: GO

  10:  

  11: SELECT *

  12: FROM dbo.Person

 

此時您會看到如下圖的錯誤訊息，告訴您已經有兩筆資料 INSERT 成功，但第三筆資料因為 EMailAddress 長度小於文字檔的內容而 INSERT 失敗。

 

這個時候若查詢 Person 資料表您可以看到真的有兩筆資料被 INSERT 進去（如下圖所示）。

【問題發生原因】

使用 BULK INSERT 預設整個匯入來源檔案是被視為一個批次，也就是一個 TRANSACTION，您可以透過設定 BATCHSIZE 參數來指定多少筆資料要當作一個批次處理，另外可以搭配 ROWS_PER_BATCH 來告訴 SQL Server 您有多少筆資料要匯入，讓 SQL Server 可以依照資料筆數進行最佳化大量匯入作業。

但上述的情況似乎在錯誤發生時，沒有整批被回復，卻發生匯入兩筆成功但一筆失敗的情況，原因出在於 MAX_ERRORS 這個參數，預設 MAX_ERRORS = 10，表示當匯入資料時允許 10 個 Rows 匯入失敗，因為本範例只有三筆資料，其中一筆匯入失敗，還未達預設允許 10 次錯誤的門檻，因此不會整批被回復。

為了驗證上述所說的，達到 MAX_ERRORS 設定的門檻值才會整批回復，筆者將要匯入的文字做下列修改，故意讓錯誤數目可以增加。

接著重新執行 BULK INSERT，此時您將會發現，因為錯誤次數已達 MAX_ERRORS 的上限，因此整個批次被回復，所以 0 個資料列受影響， 如下圖所示：

【結論】

依照網友所提的問題，若您想要讓匯入一筆資料就整個批次回復，可以在使用 BULK INSERT 的時候把 MAX_ERRORS 設定為 0，只要有錯誤就整批回復。相關程式碼如下：

   1: USE tempdb

   2: GO

   3:  

   4: IF EXISTS (SELECT * FROM dbo.Person)

   5:     DELETE FROM dbo.Person

   6: GO

   7:  

   8: BULK INSERT dbo.Person FROM 'c:\temp\test.txt' WITH (FIELDTERMINATOR = ',',MAXERRORS = 0)

   9: GO

  10:  

  11: SELECT *

  12: FROM dbo.Person

【參考資料】

- BULK INSERT (Transact-SQL)

- Is SQL Server Bulk Insert Transactional?

【延伸閱讀】

- BULK INSERT Performance - 黑暗執行緒

- [SQL SERVER][Performance]bcp vs BulkInsert(tsql)