關於作者:

本文由微軟最有價值專家 MVP TerryChuang 提供。微軟十分感謝 MVP 主動地將他們的經驗與上百萬名其他技術使用者交流。MVP TerryChuang 同時也將此篇文章放在他的部落格內,歡迎您按 此處  瀏覽 MVP TerryChuang 更多的技術文章、經驗分享與觀點。

問題的來龍去脈

使用 BULK INSERT 匯入文字檔到 SQL Server 時,發生錯誤時為什麼不是整個匯入作業都回復?為了模擬這個狀況,首先使用下列 T-SQL 敘述來建立測試資料表。

USE tempdb
GO

IF OBJECT_ID('dbo.Users') IS NOT NULL
DROP TABLE dbo.Users
GO

CREATE TABLE [dbo].[Users]
(
[FirstName] [varchar](10) NULL,
[LastName] [varchar](10) NULL,
[Email] [varchar](15) NULL
)

GO

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



最後執行下列的 BULK INSERT 敘述

USE tempdb
GO

IF EXISTS (SELECT * FROM dbo.Users)
DELETE FROM dbo.Users
GO

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

SELECT *
FROM dbo.Users

此時您會看到如下圖的錯誤訊息,告訴您已經有兩筆資料 INSERT 成功,但第二筆資料因為 EMail 欄位的長度小於文字檔的內容而 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,只要有錯誤就整批回復,另外您也可以搭配 TRY .. CATCH  來進行例外處理,相關程式碼如下:

USE tempdb
GO

IF EXISTS (SELECT * FROM dbo.Users)
DELETE FROM dbo.Users
GO

BEGIN TRY
BULK INSERT dbo.Users FROM 'c:\temp\test.txt' WITH (FIELDTERMINATOR = ',',MAXERRORS=0)
PRINT 'BULK INSERT 成功'
END TRY
BEGIN CATCH
PRINT 'BULK INSERT 失敗'
END CATCH
GO

SELECT *
FROM dbo.Users


其他相關資訊

BULK INSERT (Transact-SQL)

請讓我們知道

還滿意我們寫的內容嗎?無論您是遇到了以下任何一種情況:
「太好了!問題解決了。」
「問題沒有解決,我有更好的建議。」
都歡迎您利用本篇文章底部的意見調查表,寫下您寶貴的意見。也期待您的鼓勵,讓我們為您創造更多實用的技術文章。

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×