使用 BULK INSERT 或 OPENROWSET(BULK...) 將資料匯入 SQL Server
適用於：
SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體
本文提供一個概觀，說明如何使用 Transact-SQL BULK INSERT 陳述式與 INSERT...SELECT * FROM OPENROWSET(BULK...) 陳述式，從資料檔案大量匯入資料到 SQL Server 或 Azure SQL Database 資料表中。 本文也會描述有關使用 BULK INSERT 和 OPENROWSET(BULK…)，以及使用這些方法從遠端資料來源大量匯入時的安全性考量。
注意
當您使用 BULK INSERT 或 OPENROWSET(BULK…) 時，了解 SQL Server 版本處理模擬的方式相當重要。 如需詳細資訊，請參閱本主題稍後的「安全性考量」。
BULK INSERT 陳述式
BULK INSERT 會從資料檔案將資料載入資料表。 此功能與 bcp 命令的 in 選項相似，但卻是由 SQL Server 處理序來讀取資料檔案。 如需 BULK INSERT 語法的描述，請參閱 BULK INSERT (Transact-SQL)。
BULK INSERT 範例
- BULK INSERT
- 大量匯入與匯出 XML 文件的範例
- 大量匯入資料時保留識別值
- 大量匯入期間保留 Null 或使用預設值
- 指定欄位與資料列結束字元
- 使用格式檔案大量匯入資料
- 使用字元格式匯入或匯出資料
- 使用原生格式匯入或匯出資料
- 使用 Unicode 字元格式匯入或匯出資料
- 使用 Unicode 原生格式匯入或匯出資料
- 使用格式檔案以略過資料表資料行
- 使用格式檔案將資料表資料行對應至資料檔案欄位
OPENROWSET(BULK...)函式
OPENROWSET BULK 資料列集提供者可透過呼叫 OPENROWSET 函數及指定 BULK 選項加以存取。 OPENROWSET(BULK…) 函數可讓您透過 OLE DB 提供者連接到遠端資料來源 (例如資料檔案)，以存取遠端資料。
若要大量匯入資料，請從 INSERT 陳述式內的 SELECT…FROM 子句呼叫 OPENROWSET(BULK…)。 大量匯入資料的基本語法是：
INSERT ...SELECT * FROM OPENROWSET(BULK...)
當用於 INSERT 陳述式時，OPENROWSET(BULK...) 支援資料表提示。 除了一般的資料表提示 (例如 TABLOCK) 之外，BULK 子句也接受下列特殊化資料表提示：IGNORE_CONSTRAINTS (僅忽略 CHECK 條件約束)、IGNORE_TRIGGERS、KEEPDEFAULTS 及 KEEPIDENTITY。 如需詳細資訊，請參閱資料表提示 (Transact-SQL)。
如需 BULK 選項其他用法的資訊，請參閱 OPENROWSET (Transact-SQL)。
INSERT...SELECT * FROM OPENROWSET(BULK...) 陳述式 - 範例
- 大量匯入與匯出 XML 文件的範例
- 大量匯入資料時保留識別值
- 大量匯入期間保留 Null 或使用預設值
- 使用格式檔案大量匯入資料
- 使用字元格式匯入或匯出資料
- 使用格式檔案以略過資料表資料行
- 使用格式檔案略過資料欄位
- 使用格式檔案將資料表資料行對應至資料檔案欄位
安全性考量
如果使用者使用 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 處理序的安全性設定檔無關。
例如，有個使用者使用 Windows 驗證登入了 SQL Server 執行個體。 如果這個使用者要用 BULK INSERT 或 OPENROWSET 從資料檔案匯入資料到 SQL Server 資料表中，則使用者帳戶必須具有資料檔案的讀取權限。 有了資料檔案的存取權之後，即使 SQL Server 處理序沒有權限存取檔案，使用者還是可以將檔案中的資料匯入到資料表。 使用者不需要將檔案存取權限授與 SQL Server 處理序。
SQL Server 和 Microsoft Windows 可以設定為，透過轉送已驗證 Windows 使用者的認證，讓 SQL Server 執行個體連接到另一個 SQL Server 執行個體。 此設置也稱為「模擬」或「委派」。 當您使用 BULK INSERT 或 OPENROWSET 時，請務必了解 SQL Server 版本如何處理使用者模擬的安全性。 使用者模擬允許資料檔案位於和 SQL Server 處理序或使用者不同的電腦上。 例如，如果位於 Computer_A 的使用者可以存取 Computer_B 上的資料檔案，且已適當設定認證委派，則使用者可以連接到執行於 Computer_C 上執行的 SQL Server 執行個體，然後存取 Computer_B 上的資料檔案，並從該檔案大量匯入資料到 Computer_C 上的資料表。
從遠端資料檔案大量匯入至 SQL Server
若要使用 BULK INSERT 或 INSERT...SELECT * FROM OPENROWSET(BULK...) 從另一部電腦大量匯入資料，則必須在兩部電腦之間共用資料檔案。 若要指定共用資料檔案，請使用它的通用命名慣例 (UNC) 名稱，其使用一般格式：\\<伺服器名稱>\<共用名稱>\<路徑>\<檔案名稱>。 此外，用來存取資料檔案的帳戶必須擁有在遠端磁碟上讀取檔案所需的權限。
例如，下列 BULK INSERT 陳述式會從名為 SalesOrderDetail 的資料檔案大量匯入資料到 AdventureWorks 資料庫的 newdata.txt資料表。 此資料檔案位於 \dailyorders 系統上的 salesforce 網路共用目錄上的 computer2共用資料夾中。
BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
FROM '\\computer2\salesforce\dailyorders\neworders.txt';
注意
這個限制並不適用於 bcp 公用程式，因為用戶端可以獨立讀取檔案，不受 SQL Server 影響。
從 Azure Blob 儲存體大量匯入
從 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 SQL。 SQL Server 不支援受控識別。
注意
請不要使用明確交易，否則您將會收到 4861 錯誤。
使用 BULK INSERT
下列範例說明如何使用 BULK INSERT 命令，從建立 SAS 金鑰之 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 = '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 Database 不支援從 Windows 檔案讀取。
使用 OPENROWSET
下列範例說明如何使用 OPENROWSET 命令，從已建立 SAS 金鑰的 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 = '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!
);
INSERT INTO achievements with (TABLOCK) (id, description)
SELECT * FROM OPENROWSET(
BULK 'csv/achievements.csv',
DATA_SOURCE = 'MyAzureBlobStorage',
FORMAT ='CSV',
FORMATFILE='csv/achievements-c.xml',
FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;
重要
Azure SQL Database 不支援從 Windows 檔案讀取。
另請參閱
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應