BULK INSERT (Transact-SQL)

文章
05/04/2023

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

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

語法

BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] DATA_SOURCE = 'data_source_name' ]

   -- text formatting options
   [ [ , ] CODEPAGE = { 'RAW' | 'code_page' | 'ACP' | 'OEM' } ]
   [ [ , ] DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' } ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']

   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] LASTROW = last_row ]

   -- input file format options
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   -- error handling options
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]

   -- database options
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] TABLOCK ]

   -- source options
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch 
   [ [ , ] BATCHSIZE = batch_size ]

    )]

引數

database_name

指定的資料表或檢視表所在的資料庫名稱。如果未指定，database_name 會是目前的資料庫。

schema_name

指定資料表或檢視表結構描述的名稱。如果執行大量匯入作業之使用者的預設結構描述，是指定之資料表或檢視表的結構描述，則 schema_name 為選擇性。如果未指定 schema，且執行大量匯入作業之使用者的預設結構描述與指定的資料表或檢視表不同，SQL Server 就會傳回錯誤訊息，且會取消大量匯入作業。

table_name

指定要大量匯入資料到其中之資料表或檢視表的名稱。您只能使用所有資料行都參考相同基底資料表的檢視表。如需有關將資料載入檢視表中之限制的詳細資訊，請參閱 INSERT (Transact-SQL)。

FROM 'data_file'

指定含有要匯入至指定的資料表或檢視表中之資料的資料檔案完整路徑。 BULK INSERT 可以從磁碟或 Azure Blob 儲存體中匯入資料 (其中包括網路、磁碟片、硬碟等)。

data_file 必須指定執行 SQL Server 之伺服器中的有效路徑。如果 data_file 是一個遠端檔案，請指定「通用命名慣例」(UNC) 名稱。 UNC 名稱的格式為 \\SystemName\ShareName\Path\FileName。例如：

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.dat';

從 SQL Server 2017 (14.x) 開始，data_file 可位於 Azure Blob 儲存體中。在此情況下，您必須指定 data_source_name 選項。如需範例，請參閱從 Azure Blob 儲存體中的檔案匯入資料。

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

BATCHSIZE = batch_size

指定批次中的資料列數。每個批次都會當做一筆交易複製到伺服器中。如果失敗，SQL Server 會認可或復原每個批次的交易。依預設，指定之資料檔中的所有資料都是單一批次。如需有關效能考量的詳細資訊，請參閱本文稍後的效能考量。

CHECK_CONSTRAINTS

指定在大量匯入作業期間，必須檢查目標資料表或檢視表的所有條件約束。當沒有 CHECK_CONSTRAINTS 選項時，會忽略所有 CHECK 和 FOREIGN KEY 條件約束，而且在作業之後，會將資料表的條件約束標記為不受信任。

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

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

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

注意

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

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

指定資料檔案中之資料的字碼頁。只有當資料包含字元值大於 127 或小於 32 的 char、varchar 或 text 資料行時，CODEPAGE 才會相關。如需範例，請參閱指定字碼頁。

Linux for SQL Server 2017 (14.x) 不支援 CODEPAGE 選項。針對 SQL Server 2019 (15.x)，CODEPAGE 只允許使用 'RAW' 選項。

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

CODEPAGE 值	描述
ACP	將 char、varchar 或 text 資料類型的資料行，從 ANSI/Microsoft Windows 字碼頁 (ISO 1252) 轉換成 SQL Server 字碼頁。
OEM (預設值)	將 char、varchar 或 text 資料類型的資料行，從系統 OEM 字碼頁轉換成 SQL Server 字碼頁。
RAW	不會將字碼頁轉換成另一種字碼頁。 RAW 是最快的選項。
code_page	特定字碼頁編號，如 850。 SQL Server 2016 (13.x) 之前的版本不支援字碼頁 65001 (UTF-8 編碼)。

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

指定 BULK INSERT 利用指定的資料檔案類型值來執行匯入作業。

DATAFILETYPE 值	所有資料的表示方式如下：
char (預設值)	字元格式。如需詳細資訊，請參閱使用 Unicode 字元格式匯入或匯出資料 (SQL Server)。
native	原生 (資料庫) 資料類型。請利用 bcp 公用程式，從 SQL Server 大量匯入資料來建立原生資料檔案。原生值提供了效能比 char 值更高的替代項。在多個 SQL Server 執行個體之間，使用不包含任何擴充/雙位元組字集 (DBCS) 字元的資料檔大量傳送資料時，建議使用原生格式。如需詳細資訊，請參閱使用原生格式匯入或匯出資料 (SQL Server)。
widechar	Unicode 字元。如需詳細資訊，請參閱使用 Unicode 字元格式匯入或匯出資料 (SQL Server)。
widenative	原生 (資料庫) 資料類型，但在 char、varchar 及 text 資料行中除外，其中資料會儲存成 Unicode。請利用 bcp 公用程式，從 SQL Server 大量匯入資料來建立 widenative 資料檔案。 widenative 值是效能比 widechar 更高的替代方案。如果資料檔案包含 ANSI 擴充字元，請指定 widenative。如需詳細資訊，請參閱使用 Unicode 原生格式匯入或匯出資料 (SQL Server)。

DATA_SOURCE = 'data_source_name'

適用於：SQL Server 2017 (14.x) 和 Azure SQL Database。

指定具名的外部資料來源，指向將匯入之檔案的 Azure Blob 儲存體位置。必須使用 SQL Server 2017 (14.x) 中新增的 TYPE = BLOB_STORAGE 選項來建立外部資料來源。如需詳細資訊，請參閱 CREATE EXTERNAL DATA SOURCE。如需範例，請參閱從 Azure Blob 儲存體中的檔案匯入資料。

ERRORFILE = 'error_file_path'

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

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

從 SQL Server 2017 (14.x) 開始，error_file_path 可位於 Azure Blob 儲存體中。

ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name'

適用於：SQL Server 2017 (14.x)。

指定具名的外部資料來源，指向錯誤檔案的 Azure Blob 儲存體位置，該檔案將包含在匯入期間發現的錯誤。必須使用 SQL Server 2017 (14.x) 中新增的 TYPE = BLOB_STORAGE 選項來建立外部資料來源。如需詳細資訊，請參閱 CREATE EXTERNAL DATA SOURCE。

FIRSTROW = first_row

指定要載入之第一個資料列的號碼。預設值是指定之資料檔案中的第一個資料列。 FIRSTROW 是以 1 為基底。

FIRSTROW 屬性不是用來跳過資料行標頭。 BULK INSERT 陳述式不支援跳過標頭。如果您選擇跳過資料列，SQL Server 資料庫引擎只會查看欄位結束字元，而且不會驗證跳過資料列欄位中的資料。

FIRE_TRIGGERS

指定在大量匯入作業期間，執行目的地資料表上所定義的任何插入觸發程序。如果在目標資料表上定義了 INSERT 作業的觸發程序，便會針對每個已完成的批次引發觸發程序。

如果未指定 FIRE_TRIGGERS，便不會執行插入觸發程序。

FORMATFILE_DATA_SOURCE = 'data_source_name'

適用於：SQL Server 2017 (14.x)。

指定具名的外部資料來源，指向格式檔案的 Azure Blob 儲存體位置，該檔案將定義所匯入資料的結構描述。必須使用 SQL Server 2017 (14.x) 中新增的 TYPE = BLOB_STORAGE 選項來建立外部資料來源。如需詳細資訊，請參閱 CREATE EXTERNAL DATA SOURCE。

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 指定的值應該與實際的資料列數大約相同。如需有關效能考量的詳細資訊，請參閱本文稍後的效能考量。

TABLOCK

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

資料行存放區索引的鎖定行為不同，因為其是在內部分成多個資料列集。每個執行緒都會藉由在允許以並行資料載入工作階段進行平行資料載入的資料列集上採取 X 鎖定，以獨佔方式將資料載入到每個資料列集。使用 TABLOCK 選項將導致執行緒在資料表上採取 X 鎖定 (不同於傳統資料列集的 BU 鎖定)，這會防止其他並行執行緒同時載入資料。

輸入檔案格式選項

FORMAT = 'CSV'

適用於：SQL Server 2017 (14.x)。

指定符合 RFC 4180 標準的逗號分隔值檔案。

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT = 'CSV');

FIELDQUOTE = 'field_quote'

適用於：SQL Server 2017 (14.x)。

指定將用來當作 CSV 檔案中引號字元的字元。如果未指定，則會使用引號字元 (") 當作引號字元，如 RFC 4180 標準中所定義的。

FORMATFILE = 'format_file_path'

指定格式檔的完整路徑。格式檔描述包含預存回應的資料檔案，這些預存回應是利用 bcp 公用程式在相同資料表或檢視表上建立的。在下列情況下，應該使用格式檔：

資料檔案包含比資料表或檢視表更多或更少的資料行。
資料行的順序不同。
資料行分隔符號不同。
資料格式有其他變更。格式檔通常是利用 bcp 公用程式來建立的，您可以視需要利用文字編輯器來修改它。如需詳細資訊，請參閱 bcp 公用程式和建立格式檔案。

從 SQL Server 2017 (14.x) 開始以及在 Azure SQL Database 中，format_file_path 可以位於 Azure Blob 儲存體中。

FIELDTERMINATOR = 'field_terminator'

指定要用於 char 和 widechar 資料檔案的欄位結束字元。預設欄位結束字元是 \t (定位字元)。如需詳細資訊，請參閱指定欄位與資料列結束字元 (SQL Server)。

ROWTERMINATOR = 'row_terminator'

指定要用於 char 和 widechar 資料檔案的資料列結束字元。預設資料列結束字元是 \r\n (新行字元)。如需詳細資訊，請參閱指定欄位與資料列結束字元 (SQL Server)。

相容性

對於從檔案中讀取的資料，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)。

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

此範例會在 bulktest 資料庫中使用下表：

CREATE TABLE dbo.t_float(c1 FLOAT, c2 DECIMAL (5,4));

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

8.0000000000000002E-2 8.0000000000000002E-2

複製此範本時，請注意會將定位字元 (\t) 儲存為空格的不同文字編輯器和編碼。本範本稍後會出現定位字元。

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

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

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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.dbo.t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE = 'C:\t_floatformat-c-xml.xml');

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

大量匯出或匯入 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 2017 (14.x) 開始，BULK INSERT 可支援 CSV 格式，如同 Azure SQL Database。

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

記錄行為

如需大量匯入 SQL Server 所執行的資料列插入作業於何時記錄到交易記錄的資訊，請參閱大量匯入採用最低限度記錄的必要條件。 Azure SQL Database 不支援最低限度記錄。

限制

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

效能考量

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

建議您利用您的資料負荷量測試各種批次大小來找出最適合您的狀況。請記住，批次大小具有部分復原隱含。如果您的程序失敗並且是在再次使用 BULK INSERT 之前，您可能需要進行其他手動作業來移除失敗發生前成功插入的資料列部分。

使用 Azure SQL Database 時，如果您要匯入大量資料，請考慮在匯入之前暫時增加資料庫或執行個體的效能層級。

安全性

安全性帳戶委派 (模擬)

如果使用者使用 SQL Server 登入，則會使用 SQL Server 處理序帳戶的安全性設定檔。使用 SQL Server 驗證的登入無法於資料庫引擎外部進行驗證。因此，一旦使用 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)。

從 Azure Blob 儲存體匯入且資料不是公用 (匿名存取) 時，請依據使用 MASTER KEY 加密的 SAS 金鑰建立 DATABASE SCOPED CREDENTIAL，然後建立要用於 BULK INSERT 命令中的外部資料來源。

或者，也可根據 MANAGED IDENTITY 建立 DATABASE SCOPED CREDENTIAL 以授權非公用儲存體帳戶中資料存取的要求。使用 MANAGED IDENTITY 時，Azure 儲存體必須透過新增儲存體 Blob 資料參與者內建的 Azure 角色型存取控制 (RBAC) 角色，其中提供必要 Azure Blob 儲存體容器的受控識別讀取/寫入存取權，藉以授與執行個體的受控識別權限。 Azure SQL 受控執行個體等服務具有系統指派的受控識別，並且可以有一個或多個使用者指派的受控識別。您可以使用系統指派的受控識別或使用者指派的受控識別來授權要求。針對授權，會使用受控執行個體的 default 身分識別 (其是主要使用者指派的受控識別或系統指派的受控識別，如果未指定使用者指派的受控識別)。如需範例，請參閱從 Azure Blob 儲存體中的檔案匯入資料。

重要

受控識別僅適用於 Azure SQL。 SQL Server 不支援受控識別。

權限

需要 INSERT 和 ADMINISTER BULK OPERATIONS 權限。在 Azure SQL Database 中，需要 INSERT 和 ADMINISTER DATABASE BULK OPERATIONS 權限。 Linux 上的 SQL Server 不支援 ADMINISTER BULK OPERATIONS 權限或 bulkadmin 角色。只有 sysadmin 可以針對 Linux 上的 SQL Server 執行大量插入。

另外，如果以下一個或多個條件成立，則需要 ALTER TABLE 權限：

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

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

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

範例

A. 使用管道來匯入檔案資料

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

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

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

B. 使用 FIRE_TRIGGERS 引數

下列範例指定 FIRE_TRIGGERS 引數。

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

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

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

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

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

注意

因為 Microsoft Windows 處理文字檔的方式，所以 \n 會自動取代為 \r\n。

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

D. 指定字碼頁

下列範例說明如何指定字碼頁。

BULK INSERT MyTable
FROM 'D:\data.csv'
WITH
( CODEPAGE = '65001'
   , DATAFILETYPE = 'char'
   , FIELDTERMINATOR = ','
);

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

E. 從 CSV 檔案匯入資料

下列範例示範如何指定 CSV 檔案，跳過標頭 (第一個資料列)，使用 ; 作為欄位結束字元，以及使用 0x0a 作為行結束字元：

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

下列範例示範如何以 UTF-8 格式 (使用 65001 的 CODEPAGE) 指定 CSV 檔案，跳過標頭 (第一個資料列)，使用 ; 作為欄位結束字元，以及使用 0x0a 作為行結束字元：

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH ( CODEPAGE = '65001'
      , FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

F. 從 Azure Blob 儲存體中的檔案匯入資料

下列範例說明如何從建立共用存取簽章 (SAS) 之 Azure Blob 儲存體位置的 CSV 檔案載入資料。 Azure Blob 儲存體儲存體位置設定為外部資料來源，其需要使用 SAS 金鑰 (在使用者資料庫中使用主要金鑰加密) 的資料庫範圍認證。

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- NOTE: Make sure that you don't have a leading ? in SAS token, and
-- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
-- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL = MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

下列範例說明如何使用 BULK INSERT 命令，使用受控識別從 Azure Blob 儲存體位置中的 CSV 檔案載入資料。 Azure Blob 儲存體位置已設定為外部資料來源。

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential 
WITH IDENTITY = 'Managed Identity';
-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);
BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

重要

受控識別僅適用於 Azure SQL。 SQL Server 不支援受控識別。

重要

Azure SQL 僅支援從 Azure Blob 儲存體讀取。

G. 從 Azure Blob 儲存體中的檔案匯入資料並指定錯誤檔

下列範例說明如何在已設定為外部資料來源並指定錯誤檔的 Azure Blob 儲存體位置中，從 CSV 檔案載入資料。您將需要一個使用共用存取簽章的資料庫範圍認證。如果在 Azure SQL Database 上執行，ERRORFILE 選項應伴隨 ERRORFILE_DATA_SOURCE，否則匯入可能會因權限錯誤而導致失敗。 ERRORFILE 中指定的檔案不應該存在容器中。

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (
         DATA_SOURCE = 'MyAzureInvoices'
         , FORMAT = 'CSV'
         , ERRORFILE = 'MyErrorFile'
         , ERRORFILE_DATA_SOURCE = 'MyAzureInvoices');

如需包含設定認證和外部資料來源的完整 BULK INSERT 範例，請參閱大量存取 Azure Blob 儲存體資料的範例。

分享方式：

BULK INSERT (Transact-SQL)

語法

引數

database_name

schema_name

table_name

FROM 'data_file'

BATCHSIZE = batch_size

CHECK_CONSTRAINTS

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

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

DATA_SOURCE = 'data_source_name'

ERRORFILE = 'error_file_path'

ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name'

FIRSTROW = first_row

FIRE_TRIGGERS

FORMATFILE_DATA_SOURCE = 'data_source_name'

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

TABLOCK

輸入檔案格式選項

FORMAT = 'CSV'

FIELDQUOTE = 'field_quote'

FORMATFILE = 'format_file_path'

FIELDTERMINATOR = 'field_terminator'

ROWTERMINATOR = 'row_terminator'

相容性

資料類型

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

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

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

備註

互通性

從 CSV 檔案匯入資料

記錄行為

限制

效能考量

安全性

安全性帳戶委派 (模擬)

權限

範例

A. 使用管道來匯入檔案資料

B. 使用 FIRE_TRIGGERS 引數

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

D. 指定字碼頁

E. 從 CSV 檔案匯入資料

F. 從 Azure Blob 儲存體中的檔案匯入資料

G. 從 Azure Blob 儲存體中的檔案匯入資料並指定錯誤檔

更多範例

另請參閱

意見反映

更多資源