BULK INSERT (Transact-SQL)

發行項
12/15/2008

更新： 2006 年 12 月 12 日

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

語法

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) 名稱。

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 會強制進行新的資料驗證和資料檢查，當現有的指令碼處理資料檔中的無效資料時，作業可能會失敗。

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

附註：
一律強制實施 UNIQUE、PRIMARY KEY 和 NOT NULL 條件約束。

附註：
MAXERRORS 選項不適合條件約束檢查。

附註：
在 SQL Server 2005 中，BULK INSERT 會強制進行新的資料驗證和資料檢查，當現有的指令碼處理資料檔中的無效資料時，作業可能會失敗。

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	原生 (資料庫) 資料類型。請利用 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 屬性不是用來略過資料行標頭。如果略過資料列，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 資料類型。

附註：
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 陳述式。利用多重批次來回復以 BULK INSERT 陳述式和 BATCHSIZE 子句將資料匯入資料表或檢視的使用者自訂交易，會回復所有送往 SQL Server 的批次。

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

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

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

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

大量匯出或匯入 SQLXML 文件

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

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

字串到十進位類型轉換

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

附註：
在 SQL Server 7.0 版和 SQL Server 2000 中，對於代表使用科學記號標記法之數值的字串，BULK INSERT 支援字串到十進位類型轉換。

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

附註：
格式檔案以 SQLFLT4 資料類型來表示 real 資料，並以 SQLFLT8 資料類型來表示 float 資料。如需有關 XML 格式檔案的詳細資訊，請參閱＜XML 格式檔案的結構描述語法＞；或者，如需有關非 XML 格式檔案的詳細資訊，請參閱＜使用 bcp 指定檔案儲存類型＞。

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

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

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"?>

<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

若要使用此格式檔案 (使用檔案名稱 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

權限

需要 INSERT 和 ADMINISTER BULK OPERATIONS 權限。另外，如果符合下列中的一或多個狀況，則需要 ALTER TABLE 權限：

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

附註：

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

附註：

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

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

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

委派安全性帳戶 (模擬)

如果 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...) 匯入大量資料＞。

範例

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

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

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

B. 使用 FIRE_TRIGGERS 觸發程序

下列範例指定 FIRE_TRIGGERS 引數。

BULK INSERT AdventureWorks.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 AdventureWorks.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

其他範例

下列主題中提供了其他的 BULK INSERT 範例：

請參閱

變更歷程記錄

版本	歷程記錄
2006 年 12 月 12 日	變更的內容更新 FIRSTROW 引數描述中的附註。更正 CHECK_CONSTRAINTS 的說明，指出這個選項會導致忽略 CHECK 和 FOREIGN KEY 條件約束。
2006 年 7 月 17 日	變更的內容更新 ORDER 引數的描述。
2006 年 4 月 14 日	新增內容：新增「其他範例」一節。變更的內容更正 ADMINISTER BULK OPERATIONS 權限的拼字。更新 ALTER TABLE 權限的需求。

2006 年 12 月 12 日

變更的內容

更新 FIRSTROW 引數描述中的附註。
更正 CHECK_CONSTRAINTS 的說明，指出這個選項會導致忽略 CHECK 和 FOREIGN KEY 條件約束。

2006 年 7 月 17 日

變更的內容

更新 ORDER 引數的描述。

2006 年 4 月 14 日

新增內容：

新增「其他範例」一節。

變更的內容

更正 ADMINISTER BULK OPERATIONS 權限的拼字。
更新 ALTER TABLE 權限的需求。

BULK INSERT (Transact-SQL)

語法

引數

備註

大量匯出或匯入 SQLXML 文件

字串到十進位類型轉換

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

權限

委派安全性帳戶 (模擬)

範例

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

B. 使用 FIRE_TRIGGERS 觸發程序

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

其他範例

請參閱

參考

其他資源

說明及資訊

變更歷程記錄

其他資源