BULK INSERT指令

BULK INSERT
以使用者指定的格式複製一個資料檔案至資料庫表或視圖中。

文法
BULK INSERT [ [ 'database_name'.][ 'owner' ].]{ 'table_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 ] ] 
        ) 
    ] 

參數
'database_name'

是包含指定表或視圖的資料庫的名稱。如果未指定，則系統預設為當前資料庫。

'owner'

是表或視圖所有者的名稱。當執行大量複製操作的使用者擁有指定的表或視圖時，owner 是可選項。如果沒有指定 owner 並且執行大量複製操作的使用者不擁有指定的表或視圖，則 Microsoft SQL Server? 將返回錯誤資訊並取消大量複製操作。

'table_name'

是大量複製資料於其中的表或視圖的名稱。只能使用那些所有的列引用相同基表所在的視圖。有關向視圖中複製資料的限制的更多資訊，請參見 INSERT。 

'data_file'

是資料檔案的完整路徑，該資料檔案包含要複製到指定表或視圖的資料。BULK INSERT 從磁碟複製資料（包括網路、磁碟片、硬碟等）。 

data_file 必須從運行 SQL Server 的伺服器指定有效路徑。如果 data_file 是遠程檔案，則請指定通用命名規則 (UNC) 名稱。

BATCHSIZE [ = batch_size ]

指定批處理中的行數。每個批處理作為一個事務複製至伺服器。SQL Server提交或復原（在失敗時）每個批處理的事務。預設情況下，指定資料檔案中的所有資料是一個批處理。

CHECK_CONSTRAINTS

指定在大量複製操作中檢查 table_name 的任何約束。預設情況下，將會忽略約束。

CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]

指定該資料檔案中資料的字碼頁。僅當資料含有字元值大於 127 或小於 32 的 char、varchar 或 text 列時，CODEPAGE 才是適用的。

CODEPAGE 值 描述 
ACP char、varchar 或 text 資料類型的列從 ANSI/Microsoft Windows 字碼頁 ISO 1252 轉換為 SQL Server 字碼頁。 
OEM（預設值） char、varchar 或 text 資料類型的列被從系統 OEM 字碼頁轉換為 SQL Server 字碼頁。 
RAW 並不進行從一個字碼頁到另一個字碼頁的轉換；這是最快的選項。 
code_page 特定的字碼頁號碼，例如 850。 


DATAFILETYPE [ = {'char' | 'native' | 'widechar' | 'widenative' } ]

指定 BULK INSERT 使用指定的預設值執行複製操作。

DATAFILETYPE 值 描述 
char（預設值） 從含有字元資料的資料檔案執行大量複製操作。 
native 使用 native（資料庫）資料類型執行大量複製操作。要裝載的資料檔案由大量複製資料建立，該複製是用 bcp 工具 + 生產力從 SQL Server 進行的。 
widechar 從含有 Unicode 字元的資料檔案中執行大量複製操作。 
widenative 執行與 native 相同的大量複製操作，不同之處是 char、varchar 和 text 列在資料檔案中儲存為 Unicode。要裝載的資料檔案由大量複製資料建立，該複製是用 bcp 工具 + 生產力從 SQL Server 進行的。該選項是對 widechar 選項的一個更高效能的替代，並且它用於使用資料檔案從一個運行 SQL Server 的電腦向另一個電腦傳送資料。當傳送含有 ANSI 擴充字元的資料時，使用該選項以便利用 native 模式的效能。 


FIELDTERMINATOR [ = 'field_terminator' ]

指定用於 char 和 widechar 資料檔案的欄位結束字元。預設的欄位結束字元是 \t（定位字元）。

FIRSTROW [ = first_row ]

指定要複製的第一行的行號。預設值是 1，表示在指定資料檔案的第一行。

FIRE_TRIGGERS

指定目的表中定義的任何插入觸發器將在大量複製操作過程中執行。如果沒有指定 FIRE_TRIGGERS，將不執行任何插入觸發器。

FORMATFILE [ = 'format_file_path' ]

指定一個格式檔案的完整路徑。格式檔案描述了含有儲存響應的資料檔案，這些儲存響應是使用 bcp 工具 + 生產力在相同的表或視圖中建立的。格式檔案應該用於以下情況： 

資料檔案含有比表或視圖更多或更少的列。


列使用不同的順序。


列分割符發生變化。


資料格式有其它的改變。通常，格式檔案通過 bcp 工具 + 生產力建立並且根據需要用文字編輯器修改。有關更多資訊，請參見 bcp 工具 + 生產力。 
KEEPIDENTITY

指定識別欄位的值存在於匯入檔案中。如果沒有指定 KEEPIDENTITY，在匯入的資料檔案中此列的標識值將被忽略，並且 SQL Server 將根據表建立時指定的種子值和增量值自動賦給一個唯一的值。假如資料檔案不含該表或視圖中的識別欄位，使用一個格式檔案來指定在匯入資料時，表或視圖中的識別欄位應被忽略；SQL Server 自動為此列賦予唯一的值。有關詳細資料，請參見 DBCC CHECKIDENT。 

KEEPNULLS

指定在大量複製操作中空列應保留一個空值，而不是對插入的列賦予預設值。

KILOBYTES_PER_BATCH [ = kilobytes_per_batch ]

指定每個批處理中資料的近似KB數（KB）。預設情況下，KILOBYTES_PER_BATCH 未知。

LASTROW [ = last_row ]

指定要複製的最後一行的行號。預設值是 0，表示指定資料檔案中的最後一行。

MAXERRORS [ = max_errors ]

指定在大量複製操作取消之前可能產生的錯誤的最大數目。不能被大量複製操作匯入的每一行將被忽略並且被計為一次錯誤。如果沒有指定 max_errors，預設值為 0。

ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) 

指定資料檔案中的資料如何排序。如果裝載的資料根據表中的叢集索引進行排序，則可以提高大量複製操作的效能。如果資料檔案基於不同的順序排序，或表中沒有叢集索引，ORDER 子句將被忽略。給出的列名必須是目的表中有效列。預設情況下，大容量插入操作假設資料檔案未排序。

n

是表示可以指定多列的預留位置。

ROWS_PER_BATCH [ = rows_per_batch ]

指定每一批處理資料的行數（即 rows_per_bacth）。當沒有指定 BATCHSIZE 時使用，導致整個資料檔案作為單個事務發送給伺服器。伺服器根據 rows_per_batch 最佳化大容量裝載。預設情況下，ROWS_PER_BATCH 未知。

ROWTERMINATOR [ = 'row_terminator' ]

指定對於 char 和 widechar 資料檔案要使用的行終止符。預設值是 \n（分行符號）。

TABLOCK

指定對於大量複製操作期間擷取一個表級鎖。如果表沒有索引並且指定了 TABLOCK，則該表可以同時由多個用戶端裝載。預設情況下，鎖定行為是由表選項 table lock on bulk load 決定的。只在大量複製操作期間控制鎖會減少表上的鎖爭奪，極大地提高效能。

注釋
BULK INSERT 語句能在使用者定義事務中執行。對於一個用 BULK INSERT 語句和 BATCHSIZE 子句將資料裝載到使用多個批處理的表或視圖中的使用者定義事務來說，復原它將復原所有發送給 SQL Server 的批處理。

許可權
只有 sysadmin 和 bulkadmin 固定伺服器角色成員才能執行 BULK INSERT。

樣本
本例從指定的資料檔案中匯入訂單詳細資料，該檔案使用豎杠 (|) 字元作為欄位結束字元，使用 |\n 作為行終止符。

BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|\n'
      )

本例指定 FIRE_TRIGGERS 參數。

BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      ) 

=============================================================

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
        ( 
   [ [ , ] BATCHSIZE = batch_size ]    --BATCHSIZE指令來設定在單個事務中可以插入到表中的記錄的數量
   [ [ , ] CHECK_CONSTRAINTS ]     --指定在大容量匯入操作期間，必須檢查所有對目標表或視圖的約束。若沒有 CHECK_CONSTRAINTS 選項，則所有 CHECK 和 FOREIGN KEY 約束都將被忽略，並且在此操作之後表的約束將標記為不可信。
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]  --指定該資料檔案中資料的字碼頁
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ]  --指定 BULK INSERT 使用指定的資料檔案類型值執行匯入操作。




   [ [ , ] 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' ]   --指定用於收集格式有誤且不能轉換為 OLE DB 行集的行的檔案。
        )] 

下面寫個個簡單的應用例子

bulk insert xsxt.dbo.tabletest from 'c:\data.txt'
 with(
   FIELDTERMINATOR=',',
   ROWTERMINATOR='\n'
)