BULK INSERT (Transact-SQL)

發行項
02/18/2014

依照使用者指定的格式，將資料檔匯入資料庫資料表或檢視表中。

語法

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
    ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
    )]

引數

database_name
這是指定的資料表或檢視表所在的資料庫名稱。如果未指定，這就是目前的資料庫。
schema_name
這是資料表或檢視表結構描述的名稱。如果執行大量匯入作業之使用者的預設結構描述，是指定之資料表或檢視表的結構描述，則 schema_name 是選擇性的。如果未指定 schema，且執行大量匯入作業之使用者的預設結構描述與指定的資料表或檢視表不同，SQL Server 會傳回錯誤訊息，且會取消大量匯入作業。
table_name
這是要大量匯入資料到其中之資料表或檢視表的名稱。您只能使用所有資料行都參考相同基底資料表的檢視表。如需有關將資料載入檢視表之限制的詳細資訊，請參閱＜INSERT (Transact-SQL)＞。
' data_file '
這是含有要匯入至指定的資料表或檢視表中之資料的資料檔完整路徑。 BULK INSERT 可以從磁碟中匯入資料 (其中包括網路、磁碟片、硬碟等)。

data_file 必須指定執行 SQL Server 之伺服器的有效路徑。如果 data_file 是一個遠端檔案，請指定通用命名慣例 (UNC) 名稱。 UNC 名稱的格式為 \\Systemname\ShareName\Path\FileName。例如，\\SystemX\DiskZ\Sales\update.txt。
BATCHSIZE **=**batch_size
指定批次中的資料列數。每個批次都會當做一筆交易複製到伺服器中。如果失敗，SQL Server 會認可或回復每個批次的交易。依預設，指定之資料檔中的所有資料都是單一批次。如需有關效能考量的詳細資訊，請參閱本主題稍後的＜備註＞。
CHECK_CONSTRAINTS
指定在大量匯入作業期間，必須檢查目標資料表或檢視表的所有條件約束。當沒有 CHECK_CONSTRAINTS 選項時，會忽略所有 CHECK 和 FOREIGN KEY 條件約束，而且在作業之後，會將資料表的條件約束標記為不受信任。

[!附註]

一律強制實施 UNIQUE 和 PRIMARY KEY 條件約束。當匯入到使用 NOT NULL 條件約束所定義的字元資料行中時，BULK INSERT 會在文字檔中沒有任何值時插入空白字串。

在某個點上，您必須檢查整份資料表的條件約束。如果在大量匯入作業之前，資料表不是空的，重新驗證條件約束的成本可能會超出在累加資料上套用 CHECK 條件約束的成本。

如果輸入資料包含違反條件約束的資料列，您可能會想停用條件約束 (預設行為)。當停用 CHECK 條件約束時，您可以先匯入資料，再利用 Transact-SQL 陳述式來移除無效的資料。

[!附註]

MAXERRORS 選項不適用於條件約束檢查。

[!附註]

在 SQL Server 2005 及更新版本中，BULK INSERT 會強制進行新的資料驗證和資料檢查，而當現有的指令碼針對資料檔中的無效資料執行時，這些作業可能會造成指令碼失敗。

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
指定資料檔中之資料的字碼頁。只有當資料包含字元值大於 127 或小於 32 的 char、varchar 或 text 資料行時，CODEPAGE 才會相關。

[!附註]

Microsoft 建議您在格式檔案中，針對每一個資料行各指定一個定序名稱。

ACP

char、varchar 或 text 資料類型的資料行會從 ANSI/Microsoft Windows 字碼頁 (ISO 1252) 轉換成 SQL Server 字碼頁。

OEM (預設值)

char、varchar 或 text 資料類型的資料行會從系統 OEM 字碼頁轉換成 SQL Server 字碼頁。

RAW

不進行字碼頁之間的轉換；這是最快的選項。

code_page

特定字碼頁編號，如 850。

重要事項
SQL Server 不支援字碼頁 65001 (UTF-8 編碼)。

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }
指定 BULK INSERT 利用指定的資料檔類型值來執行匯入作業。

DATAFILETYPE 值	所有資料的表示方式如下：
char (預設值)	字元格式。如需詳細資訊，請參閱＜使用字元格式匯入或匯出資料 (SQL Server)＞。
native	原生 (資料庫) 資料類型。請利用 bcp 公用程式，從 SQL Server 大量匯入資料來建立原生資料檔。原生值提供了效能比 char 值更高的替代項。如需詳細資訊，請參閱＜使用原生格式匯入或匯出資料 (SQL Server)＞。
widechar	Unicode 字元。如需詳細資訊，請參閱＜使用 Unicode 字元格式匯入或匯出資料 (SQL Server)＞。
widenative	原生 (資料庫) 資料類型，但 char、varchar 和 text 資料行除外，此時資料會儲存成 Unicode。請利用 bcp 公用程式，從 SQL Server 大量匯入資料來建立 widenative 資料檔。 widenative 值提供了效能比 widechar 值更高的替代項。如果資料檔包含 ANSI 擴充字元，請指定 widenative。如需詳細資訊，請參閱＜使用 Unicode 原生格式匯入或匯出資料 (SQL Server)＞。

FIELDTERMINATOR ='field_terminator'
指定 char 和 widechar 資料檔要用的欄位結束字元。預設欄位結束字元是 \t (定位字元)。如需詳細資訊，請參閱＜指定欄位與資料列結束字元 (SQL Server)＞。
FIRSTROW **=**first_row
指定要載入之第一個資料列的號碼。預設值是指定之資料檔中的第一個資料列。 FIRSTROW 是以 1 為基底。

[!附註]

FIRSTROW 屬性不是用來略過資料行標頭。 BULK INSERT 陳述式不支援略過標頭。如果略過資料列，SQL Server Database Engine 就只會查看欄位結束字元，而且不會驗證已略過之資料列中欄位的資料。
FIRE_TRIGGERS
指定在大量匯入作業期間，執行目的地資料表上所定義的任何插入觸發程序。如果在目標資料表上定義了 INSERT 作業的觸發程序，便會針對每個已完成的批次引發觸發程序。

如果未指定 FIRE_TRIGGERS，就不會執行任何插入觸發程序。
FORMATFILE ='format_file_path'
指定格式檔的完整路徑。格式檔描述包含預存回應的資料檔，預存回應是利用 bcp 公用程式在相同資料表或檢視表上所建立。在下列情況下，應該使用格式檔：
- 資料檔包含比資料表或檢視表更多或更少的資料行。
- 資料行的順序不同。
- 資料行分隔符號不同。
- 資料格式有其他變更。格式檔通常是利用 bcp 公用程式所建立，您可以依照需要利用文字編輯器來修改它。如需詳細資訊，請參閱＜bcp 公用程式＞。
KEEPIDENTITY
指定識別欄位要使用匯入之資料檔中的一個或多個識別值。如果未指定 KEEPIDENTITY，就會驗證這個資料行的識別值但不會匯入它，而且 SQL Server 會根據建立資料表期間所指定的種子值和遞增值來自動指派唯一值。如果資料檔中沒有資料表或檢視表中之識別欄位的值，請利用格式檔來指定，在匯入資料時略過資料表或檢視表中的識別欄位；SQL Server 會自動指派資料行的唯一值。如需詳細資訊，請參閱＜DBCC CHECKIDENT (Transact-SQL)＞。

如需有關保留識別值的詳細資訊，請參閱＜大量匯入資料時保留識別值 (SQL Server)＞。
KEEPNULLS
指定在大量匯入作業期間，空白資料行應該保留 Null 值，而不是插入資料行的任何預設值。如需詳細資訊，請參閱＜大量匯入期間保留 Null 或使用預設值 (SQL Server)＞。
KILOBYTES_PER_BATCH = kilobytes_per_batch
以 kilobytes_per_batch 指定每一批資料的近似 KB 數。依預設，KILOBYTES_PER_BATCH 是未知的。如需有關效能考量的詳細資訊，請參閱本主題稍後的「備註」。
LASTROW**=**last_row
指定要載入之最後一個資料列的號碼。預設值是 0，表示指定之資料檔中的最後一個資料列。
MAXERRORS = max_errors
指定取消大量匯入作業之前所允許的資料語法錯誤數目上限。大量匯入作業所無法匯入的每個資料列都會被忽略，且會當做一項錯誤來計算。如果未指定 max_errors，預設值是 10。

[!附註]

MAX_ERRORS 選項不適用於條件約束檢查，也不能轉換 money 和 bigint 資料類型。
ORDER ( { column [ ASC | DESC ] } [ ,...n ] )
指定如何排序資料檔中的資料。如果匯入資料時是依照資料表的叢集索引來排序，將可提升大量匯入的效能。如果資料檔是依據不同於叢集索引鍵順序的其他順序來進行排序，或是資料表上沒有任何叢集索引，便會略過 ORDER 子句。提供的資料行名稱必須是目的地資料表中的有效資料行名稱。依預設，大量插入作業會假設資料檔沒有排序。為了達到最佳的大量匯入效果，SQL Server 也會驗證匯入的資料是否已排序。
n
這是一個預留位置，表示可以指定多個資料行。
ROWS_PER_BATCH **=**rows_per_batch
指出資料檔中大約有多少資料列。

依預設，資料檔中的所有資料都會當做單一交易來傳給伺服器，而且查詢最佳化工具並不知道批次中的資料列數。如果您指定 ROWS_PER_BATCH (利用 > 0 的值)，伺服器會利用這個值來最佳化大量匯入作業。 ROWS_PER_BATCH 指定的值應該與實際的資料列數大約相同。如需有關效能考量的詳細資訊，請參閱本主題稍後的「備註」。
ROWTERMINATOR ='row_terminator'
指定 char 和 widechar 資料檔要用的資料列結束字元。預設資料列結束字元是 \r\n (新行字元)。如需詳細資訊，請參閱＜指定欄位與資料列結束字元 (SQL Server)＞。
TABLOCK
指定在大量匯入作業期間，取得資料表層級鎖定。如果資料表沒有索引，且指定了 TABLOCK，多個用戶端便可以同時載入這份資料表。根據預設，鎖定行為是由資料表選項 table lock on bulk load 所決定。在大量匯入作業期間保留鎖定，會減少競爭資料表鎖定的情況，在某些情況下，可以大幅提升效能。如需有關效能考量的詳細資訊，請參閱本主題稍後的「備註」。
ERRORFILE ='file_name'
指定用來收集格式錯誤且無法轉換成 OLE DB 資料列集之資料列的檔案。這些資料列會「依照原狀」，從資料檔複製到這個錯誤檔中。

當執行命令時，便會建立這個錯誤檔。如果檔案已經存在，會發生一則錯誤。另外，還會建立一個副檔名為 .ERROR.txt 的控制檔。這會參考錯誤檔中的每個資料列，且會提供錯誤診斷。錯誤更正之後，就能夠載入資料。

相容性

對於從檔案中讀取的資料，BULK INSERT 會強制進行嚴格的資料驗證和資料檢查，而當現有的指令碼針對無效資料執行時，這些作業可能會造成指令碼失敗。例如，BULK INSERT 會驗證：

float 或 real 資料類型的原生表示法有效。
Unicode 資料的長度是否為偶數位元組。

資料類型

字串到十進位資料類型轉換

用於 BULK INSERT 的字串到十進位資料類型轉換，將遵守與 Transact-SQL CONVERT 函數相同的規則，該函數會拒絕代表使用科學記號標記法之數值的字串。因此，BULK INSERT 會將這類字串視為無效的值，並報告轉換錯誤。

若要解決這種行為，請使用格式檔案，將科學記號標記法 float 資料大量匯入至十進位資料行。在格式檔中，請將此資料行明確描述為 real 或 float 資料。如需有關這些資料類型的詳細資訊，請參閱＜float 和 real (Transact-SQL)＞。

[!附註]

格式檔案以 SQLFLT4 資料類型來表示 real 資料，並以 SQLFLT8 資料類型來表示 float 資料。如需非 XML 格式檔案的詳細資訊，請參閱＜使用 bcp 指定檔案儲存類型 (SQL Server)＞。

匯入使用科學記號標記法之數值的範例

這個範例使用下列資料表：

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

使用者想要將大量資料匯入 t_float 資料表中。資料檔 C:\t_float-c.dat 包含科學記號標記法 float 資料；例如：

8.0000000000000002E-28.0000000000000002E-2

不過，BULK INSERT 無法直接將此資料匯入 t_float 中，因為它的第二個資料行 c2 使用 decimal 資料類型。因此，格式檔案是必要的。格式檔案必須將科學記號標記法 float 資料對應到資料行 c2 的十進位格式。

下列格式檔案使用 SQLFLT8 資料類型，將第二個資料欄位對應到第二個資料行：

<?xml version="1.0"?>

若要使用此格式檔案 (使用檔案名稱 C:\t_floatformat-c-xml.xml) 將測試資料匯入測試資料表，請發出下列 Transact-SQL 陳述式：

BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

大量匯出或匯入 SQLXML 文件的資料類型

若要大量匯出或匯入 SQLXML 資料，請在格式檔案中使用下列其中一種資料類型：

資料類型	效果
SQLCHAR 或 SQLVARCHAR	資料是使用用戶端字碼頁或定序所隱含的字碼頁所傳送。這與指定 DATAFILETYPE = 'char' 而不指定格式檔案的效果是一樣的。
SQLNCHAR 或 SQLNVARCHAR	以 Unicode 格式傳送這份資料。這與指定 DATAFILETYPE = 'widechar' 而不指定格式檔案的效果是一樣的。
SQLBINARY 或 SQLVARBIN	未經任何轉換即傳送這份資料。

一般備註

如需 BULK INSERT 陳述式、INSERT ... SELECT * FROM OPENROWSET(BULK...) 陳述式和 bcp 命令的比較，請參閱＜資料的大量匯入及匯出 (SQL Server)＞。

如需有關準備資料進行大量匯入的詳細資訊，請參閱＜準備大量匯出或匯入的資料 (SQL Server)＞。

您可以在使用者定義交易內部執行 BULK INSERT 陳述式，以便將資料匯入資料表或檢視表。 (選擇性) 若要針對大量匯入資料使用多重比對，交易可以在 BULK INSERT 陳述式中指定 BATCHSIZE 子句。如果多重批次交易已回復，交易已經傳送至 SQL Server 的每個批次都會回復。

互通性

從 CSV 檔案匯入資料

SQL Server 大量匯入作業不支援逗號分隔值 (CSV) 檔案。不過，在某些情況下，CSV 檔案可用來當做資料檔，以便將資料大量匯入 SQL Server。如需有關從 CSV 資料檔匯入資料之需求的詳細資訊，請參閱＜準備大量匯出或匯入的資料 (SQL Server)＞。

記錄行為

如需有關大量匯入所執行的資料列插入作業於何時記錄到交易記錄的詳細資訊，請參閱＜大量匯入採用最低限度記錄的必要條件＞。

限制

使用格式檔案搭配 BULK INSERT 時，最多只能指定 1024 個欄位。這與資料表中允許的資料行數目上限相同。如果您使用 BULK INSERT 搭配包含超過 1024 個欄位的資料檔，則 BULK INSERT 會產生 4822 錯誤。 bcp 公用程式沒有此限制，因此，請針對包含超過 1024 個欄位的資料檔使用 bcp 命令。

效能考量

如果要在單一批次中排清的頁數超出內部臨界值，可能會發生緩衝集區的完整掃描，以識別批次認可時要排清的頁面。這個完整掃描可能會損及大量匯入效能。當大型緩衝集區與緩慢的 I/O 子系統結合時，可能會超出內部臨界值。為避免大型電腦發生緩衝區溢位，請不要使用 TABLOCK 提示 (將會移除大量最佳化) 或使用較小的批次大小 (可保留大量最佳化)。

電腦會不斷變化，因此，我們建議您利用您的資料負荷量測試各種批次大小來找出最適合您的狀況。

安全性

委派安全性帳戶 (模擬)

如果使用者是使用 SQL Server 登入，則會使用 SQL Server 處理序帳戶的安全性設定檔。使用 SQL Server 驗證的登入無法於 Database Engine 外部進行驗證。因此，一旦使用 SQL Server 驗證的登入起始 BULK INSERT 命令，將會使用 SQL Server 處理序帳戶 (即 SQL Server Database Engine 服務所使用的帳戶) 的安全性內容建立與資料的連接。為了能夠成功讀取來源資料，您必須授與 SQL Server Database Engine 所使用的帳戶對來源資料的存取權。相反地，如果 SQL Server 使用者是使用 Windows 驗證登入，則該使用者只能讀取其使用者帳戶可存取的檔案，而與 SQL Server 處理序的安全性設定檔無關。

當利用 sqlcmd 或 osql 來執行 BULK INSERT 陳述式、將一部電腦的資料插入第二部電腦的 SQL Server 中，以及利用 UNC 路徑在第三部電腦中指定 data_file 時，您可能會收到 4861 錯誤。

若要解決這個錯誤，請使用 SQL Server 驗證以及指定 SQL Server 登入，此時會使用 SQL Server 處理序帳戶的安全性設定檔，或設定 Windows 來啟用安全性帳戶的委派。如需有關如何使某個使用者帳戶受到信任而委派的詳細資訊，請參閱 Windows 說明。

如需有關這個主題以及使用 BULK INSERT 之其他安全性考量的詳細資訊，請參閱＜使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大量資料(SQL Server)＞。

權限

需要 INSERT 和 ADMINISTER BULK OPERATIONS 權限。另外，如果以下一個或多個狀況成立，則需要 ALTER TABLE 權限：

有條件約束存在而且未指定 CHECK_CONSTRAINTS 選項。

[!附註]

停用條件約束是預設行為。若要明確檢查條件約束，請使用 CHECK_CONSTRAINTS 選項。
有觸發程序存在而且未指定 FIRE_TRIGGER 選項。

[!附註]

依預設不會引發觸發程序。若要明確引發觸發程序，請使用 FIRE_TRIGGER 選項。
您利用 KEEPIDENTITY 選項，從資料檔中匯入識別值。

範例

A.利用垂直線來匯入檔案資料

下列範例會從指定的資料檔中，將訂單詳細資訊匯入 AdventureWorks.Sales.SalesOrderDetail 資料表中，方法是利用垂直線 (|) 做為欄位結束字元，並利用 |\n 做為資料列結束字元。

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      );

B.使用 FIRE_TRIGGERS 觸發程序

下列範例指定 FIRE_TRIGGERS 引數。

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR =' |',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      );

C.利用換行字元做為資料列結束字元

下列範例利用換行字元做為資料列結束字元來匯入檔案，如 UNIX 輸出：

DECLARE @bulk_cmd varchar(1000);
SET @bulk_cmd = 'BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')';
EXEC(@bulk_cmd);