BULK INSERT (Transact-SQL)

語法

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 條件約束。

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

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

  [!附註]

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

  [!附註]

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

  如需詳細資訊，請參閱＜控制大量匯入作業的條件約束檢查＞。

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

  [!附註]

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

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

• FIELDTERMINATOR ='field_terminator'
  指定 char 和 widechar 資料檔要用的欄位結束字元。預設欄位結束字元是 \t (定位字元)。如需詳細資訊，請參閱＜指定欄位和資料列結束字元＞。

• 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)＞。

  如需有關保留識別值的詳細資訊，請參閱＜大量匯入資料時保留識別值＞。

• KEEPNULLS
  指定在大量匯入作業期間，空白資料行應該保留 Null 值，而不是插入資料行的任何預設值。如需詳細資訊，請參閱＜在大量匯入期間保留 Null 或使用預設值＞。

• 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 (新行字元)。如需詳細資訊，請參閱＜指定欄位和資料列結束字元＞。

• TABLOCK
  指定在大量匯入作業期間，取得資料表層級鎖定。如果資料表沒有索引，且指定了 TABLOCK，多個用戶端便可以同時載入這份資料表。依預設，鎖定行為是由資料表選項 table lock on bulk load (大量載入時鎖定資料表) 來決定。在大量匯入作業期間保留鎖定，會減少爭用資料表鎖定的情況，在某些情況下，可以大幅提升效能。如需有關效能考量的詳細資訊，請參閱本主題稍後的「備註」。

  如需詳細資訊，請參閱＜控制大量匯入的鎖定行為＞。

• ERRORFILE ='file_name'
  指定用來收集格式錯誤且無法轉換成 OLE DB 資料列集之資料列的檔案。這些資料列會「依照原狀」，從資料檔複製到這個錯誤檔中。

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

備註

如需 BULK INSERT 陳述式、INSERT ...SELECT * FROM OPENROWSET(BULK...) 陳述式和 bcp 命令的比較，請參閱＜關於大量匯入和大量匯出作業＞。

如需有關準備大量匯入資料的詳細資訊，例如從 CSV 資料檔匯入資料的需求，請參閱＜準備大量匯出或匯入的資料＞。

在使用者定義的交易內，可以執行 BULK INSERT 陳述式。利用多重批次來回復以 BULK INSERT 陳述式和 BATCHSIZE 子句將資料匯入資料表或檢視表的使用者定義交易時，會回復所有送往 SQL Server 的批次。

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

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

• float 或 real 資料類型的原生表示法是否有效。

• Unicode 資料的長度是否為偶數位元組。

舊版 SQL Server 能夠大量匯入的各種無效資料形式，現在可能會無法載入。在舊版的 SQL Server 中，當用戶端尚未嘗試存取無效資料時，作業不會失敗。更嚴格的驗證會使大量匯入之後的資料查詢，將出現意外的狀況減到最少。

效能考量

大量匯出或匯入 SQLXML 文件

字串到十進位類型轉換

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

8.0000000000000002E-28.0000000000000002E-2

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

權限

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

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

  [!附註]

  停用條件約束是預設行為。若要明確檢查條件約束，請使用 CHECK_CONSTRAINTS 選項。

• 有觸發程序存在而且未指定 FIRE_TRIGGER 選項。

  [!附註]

  依預設不會引發觸發程序。若要明確引發觸發程序，請使用 FIRE_TRIGGER 選項。

• 您利用 KEEPIDENTITY 選項，從資料檔中匯入識別值。

委派安全性帳戶 (模擬)

範例

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

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

B. 使用 FIRE_TRIGGERS 觸發程序

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

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

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

其他範例

變更記錄

請參閱

參考

概念

其他資源