插入大量資料

Transact-SQL 參考

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）。預設情況下，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
      )
      

請參見

bcp 實用工具

排序規則

在不同排序規則間複制資料

使用 bcp 和 BULK INSERT

并行資料裝載

sp_tableoption

使用格式檔案

©1988-2000 Microsoft Corporation。保留所有權利。