共用方式為

使用 BULK INSERT 或 OPENROWSET(BULK...) 將資料匯入 SQL Server

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

本文提供如何使用 Transact-SQL BULK INSERT 語句和 INSERT...SELECT * FROM OPENROWSET(BULK...) 語句，將數據從數據檔大容量導入 SQL Server 或 Azure SQL Database 數據表的概觀。

本文也說明使用 BULK INSERT 和 OPENROWSET(BULK...)的安全性考慮，以及使用這些方法從遠端數據源大容量導入。

BULK INSERT 陳述式

BULK INSERT 將數據從數據檔載入資料表。 這項功能類似於in命令的bcp選項所提供的功能。 不過，SQL Server 進程會讀取數據檔。 如需BULK INSERT語法的描述，請參閱BULK INSERT （Transact-SQL）。

BULK INSERT 範例

OPENROWSET(BULK...)函式

呼叫 OPENROWSET 函式並指定 OPENROWSET 選項，即可存取批量資料列集提供者。 函 OPENROWSET(BULK...) 式可讓您透過資料提供者連線到遠端數據源，例如數據檔，以存取遠端數據。

若要大容量匯入資料，請從 OPENROWSET(BULK...) 語句內的 SELECT...FROM 子句呼叫 INSERT 。

大量匯入資料的基本語法是：

INSERT ... SELECT * FROM OPENROWSET(BULK...)

在語句中使用 INSERT 時， OPENROWSET(BULK...) 支持數據表提示。 除了一般數據表提示，例如 TABLOCK， BULK 子句可以接受下列特製化數據表提示：

  • IGNORE_CONSTRAINTS （只忽略 CHECK 條件約束）
  • IGNORE_TRIGGERS
  • KEEPDEFAULTS
  • KEEPIDENTITY

如需詳細資訊，請參閱 資料表提示 （Transact-SQL） 。

如需 BULK 選項之其他用法的詳細資訊，請參閱 OPENROWSET BULK （Transact-SQL） 。

INSERT...SELECT * FROM OPENROWSET(BULK...) 陳述式 - 範例

安全性考量

如果使用者使用 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） 名稱，其採用一般形式。 \\Servername\Sharename\Path\Filename

此外，用來存取資料檔案的帳戶必須擁有在遠端磁碟上讀取檔案所需的權限。

例如，下列 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 記憶體匯入且數據不是公用的（匿名存取）時，請根據以 CREATE MASTER KEY 加密的 SAS 金鑰建立 CREATE DATABASE SCOPED CREDENTIAL，然後建立外部資料庫來源以供命令BULK INSERT使用。

或者，根據 建立 MANAGED IDENTITY，以授權非公用儲存帳戶中的資料存取請求。 當使用 MANAGED IDENTITY 時，Azure 儲存體必須將 儲存體 Blob 資料參與者 這個內建的 Azure 角色型存取控制 (RBAC) 角色新增給實例的受控身份，這個角色提供受控身份對必要的 Azure Blob 儲存體容器的讀取和寫入權限。 Azure SQL 受控執行個體具有系統分配的受控識別，並且可以有一個或多個使用者指派的受控識別。 您可以使用系統指派的受控識別或使用者指派的受控識別來授權要求。 針對授權，會使用受控執行個體的 default 身分識別 (其是主要使用者指派的受控身分識別或系統指派的受控身分識別，如果未指定使用者指派的受控身分識別)。

重要

SQL Server 2025 之前的 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 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 檔案讀取。

相關內容

  • Last updated on