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

適用于:SQL Server (所有支援的版本) Azure SQL Database Azure 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 範例

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...) 陳述式 - 範例:

安全性考量

如果使用者使用SQL Server登入,則會使用SQL Server進程帳戶的安全性設定檔。 使用 SQL Server 驗證的登入無法於 Database Engine 外部進行驗證。 因此,一旦使用 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 可以設定為啟用 SQL Server 實例,藉由轉送已驗證 Windows 使用者的認證來連線到另一個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 AdventureWorks2012.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

注意

此限制不適用於bcp公用程式,因為用戶端會獨立于SQL Server讀取檔案。

從 Azure Blob 儲存體大量匯入

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

注意

請不要使用明確交易,否則您將會收到 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');

重要事項

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 檔案讀取。

另請參閱