使用 BULK INSERT 或 OPENROWSET(BULK...) 导入数据到 SQL ServerUse BULK INSERT or OPENROWSET(BULK...) to import data to SQL Server

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本文概述了如何使用 Transact-SQLTransact-SQL BULK INSERT 语句和 INSERT...SELECT * FROM OPENROWSET(BULK...) 语句将数据从某一数据文件批量导入到 SQL ServerSQL Server 或 Azure SQL 数据库表中。This article provides an overview of how to use the Transact-SQLTransact-SQL BULK INSERT statement and the INSERT...SELECT * FROM OPENROWSET(BULK...) statement to bulk import data from a data file into a SQL ServerSQL Server or Azure SQL Database table. 本文还说明了使用 BULK INSERT 和 OPENROWSET(BULK...) 以及使用这些方法从远程数据源批量导入数据的安全注意事项。This article also describes security considerations for using BULK INSERT and OPENROWSET(BULK...), and using these methods to bulk import from a remote data source.

备注

在使用 BULK INSERT 或 OPENROWSET(BULK...) 时,请务必了解 SQL ServerSQL Server 版本处理模拟的方式。When you use BULK INSERT or OPENROWSET(BULK...), it is important to understand how SQL ServerSQL Server version handles impersonation. 有关详细信息,请参阅本主题后面的“安全注意事项”。For more information, see "Security Considerations," later in this topic.

BULK INSERT 语句BULK INSERT statement

BULK INSERT 将数据从数据文件加载到表中。BULK INSERT loads data from a data file into a table. 此功能与 bcp 命令的 in 选项提供的功能相似,但是数据文件将由 SQL ServerSQL Server 进程读取。This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL ServerSQL Server process. 有关 BULK INSERT 语法的说明,请参阅 BULK INSERT (Transact-SQL)For a description of the BULK INSERT syntax, see BULK INSERT (Transact-SQL).

BULK INSERT 示例BULK INSERT examples

OPENROWSET(BULK…)函数OPENROWSET(BULK...) Function

通过调用 OPENROWSET 函数并指定 BULK 选项,访问 OPENROWSET 大容量行集提供程序。The OPENROWSET bulk rowset provider is accessed by calling the OPENROWSET function and specifying the BULK option. 使用 OPENROWSET(BULK...) 函数可以通过 OLE DB 访问接口连接到远程数据源(如数据文件)以访问远程数据。The OPENROWSET(BULK...) function allows you to access remote data by connecting to a remote data source, such as a data file, through an OLE DB provider.

若要大容量导入数据,请从 INSERT 语句中的 SELECT...FROM 子句调用 OPENROWSET(BULK...)。To bulk import data, call OPENROWSET(BULK...) from a SELECT...FROM clause within an INSERT statement. 大容量导入数据的基本语法如下:The basic syntax for bulk importing data is:

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

在 INSERT 语句中使用时,OPENROWSET(BULK...) 支持表提示。When used in an INSERT statement, OPENROWSET(BULK...) supports table hints. 除了常规表提示(例如 TABLOCK),BULK 子句还可以接受以下专用表提示:IGNORE_CONSTRAINTS(仅忽略 CHECK 约束)、IGNORE_TRIGGERS、KEEPDEFAULTS 和 KEEPIDENTITY。In addition to the regular table hints, such as TABLOCK, the BULK clause can accept the following specialized table hints: IGNORE_CONSTRAINTS (ignores only the CHECK constraints), IGNORE_TRIGGERS, KEEPDEFAULTS, and KEEPIDENTITY. 有关详细信息,请参阅表提示 (Transact-SQL)For more information, see Table Hints (Transact-SQL).

有关 BULK 选项的更多使用信息,请参阅 OPENROWSET (Transact-SQL)For information about additional uses of the BULK option, see OPENROWSET (Transact-SQL).

INSERT...SELECT * FROM OPENROWSET(BULK...) 语句 - 示例:INSERT...SELECT * FROM OPENROWSET(BULK...) statements - examples:

安全注意事项Security considerations

如果用户使用的是 SQL ServerSQL Server 登录名,则系统将使用 SQL ServerSQL Server 进程帐户的安全配置文件。If a user uses a SQL ServerSQL Server login, the security profile of the SQL ServerSQL Server process account is used. 使用 SQL Server 身份验证的登录名不能在数据库引擎外部进行身份验证。A login using SQL Server authentication cannot be authenticated outside of the Database Engine. 因此,当 BULK INSERT 命令由使用 SQL Server 身份验证的登录名启动时,使用 SQL Server 进程帐户(SQL Server 数据库引擎服务使用的帐户)的安全上下文建立到数据的连接。Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service).

要成功读取源数据,您必须授予 SQL Server 数据库引擎使用的帐户访问源数据的权限。To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. 与此相反,如果 SQL ServerSQL Server 用户使用 Windows 身份验证登录,则该用户只能读取用户帐户可以访问的那些文件,而不考虑 SQL ServerSQL Server 进程的安全配置文件。In contrast, if a SQL ServerSQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL ServerSQL Server process.

例如,假设用户使用 Windows 身份验证登录到某个 SQL ServerSQL Server 实例。For example, consider a user who logged in to an instance of SQL ServerSQL Server by using Windows Authentication. 对于能够使用 BULK INSERT 或 OPENROWSET 将数据从数据文件导入 SQL ServerSQL Server 表中的用户,用户帐户需要具有数据文件的读取权限。For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL ServerSQL Server table, the user account requires read access to the data file. 有了数据文件的访问权限,即使 SQL ServerSQL Server 进程没有访问该文件的权限,用户也可以将数据从文件导入表中。With access to the data file, the user can import data from the file into a table even if the SQL ServerSQL Server process does not have permission to access the file. 用户无需将文件访问权限授予 SQL ServerSQL Server 进程。The user does not have to grant file-access permission to the SQL ServerSQL Server process.

SQL ServerSQL Server MicrosoftMicrosoft Windows,使得一个 SQL ServerSQL Server 实例可以通过转发已经过身份验证的 Windows 用户的凭据来连接到另一个 SQL ServerSQL Server 实例。and MicrosoftMicrosoft Windows can be configured to enable an instance of SQL ServerSQL Server to connect to another instance of SQL ServerSQL Server by forwarding the credentials of an authenticated Windows user. 这种安排称为“模拟” 或“委托” 。This arrangement is known as impersonation or delegation. 在使用 BULK INSERT 或 OPENROWSET 时,请务必了解 SQL ServerSQL Server 版本是如何处理用户模拟的安全性的。Understanding how SQL ServerSQL Server version handle security for user impersonation is important when you use BULK INSERT or OPENROWSET. 用户模拟允许数据文件保存在 SQL ServerSQL Server 进程或用户所在的计算机以外的另一台计算机上。User impersonation allows the data file to reside on a different computer than either the SQL ServerSQL Server process or the user. 例如,如果 Computer_A 上的用户具有对 Computer_B上的数据文件的访问权限,而且凭据委托已设置妥当,则用户可以连接到运行在 SQL ServerSQL Server Computer_C 上的实例,访问 Computer_B中的数据文件以及将数据从该文件大容量导入到 Computer_C中的表中。For example, if a user on Computer_A has access to a data file on Computer_B, and the delegation of credentials has been set appropriately, the user can connect to an instance of SQL ServerSQL Server that is running on Computer_C, access the data file on Computer_B, and bulk import data from that file into a table on Computer_C.

从远程数据文件批量导入到 SQL ServerBulk importing to SQL Server from a remote data file

若要使用 BULK INSERT 或 INSERT...SELECT * FROM OPENROWSET(BULK...) 从其他计算机中大容量导入数据,必须在两台计算机之间共享数据文件。To use BULK INSERT or INSERT...SELECT * FROM OPENROWSET(BULK...) to bulk import data from another computer, the data file must be shared between the two computers. 指定共享数据文件时,请使用它的通用命名约定 (UNC) 名称,其一般形式为 \\ 服务器名 \ 共享名 \ 路径 \ 文件名To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form, \\Servername\Sharename\Path\Filename. 此外,用来访问该数据文件的帐户必须具有读取远程磁盘上的文件所需的权限。Additionally, the account used to access the data file must have the permissions that are required for reading the file on the remote disk.

例如,下面的 BULK INSERT 语句会将名为 SalesOrderDetail 的数据文件中的数据大容量导入到 AdventureWorks 数据库的 newdata.txt表。For example, the following BULK INSERT statement bulk imports data into the SalesOrderDetail table of the AdventureWorks database from a data file that is named newdata.txt. 此数据文件驻留在系统 \dailyorderssalesforce 网络共享目录下的 computer2 共享文件夹中。This data file resides in a shared folder named \dailyorders on a network share directory named salesforce on a system named computer2.

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

备注

此限制不适用于 bcp 实用工具,因为客户端独立于 SQL ServerSQL Server 读取文件。This restriction does not apply to the bcp utility because the client reads the file independently of SQL ServerSQL Server.

从 Azure Blob 存储批量导入Bulk importing from Azure Blob storage

从 Azure Blob 存储导入数据且数据非公共数据(匿名访问)时,请基于使用 MASTER KEY 加密的 SAS 密钥创建一个 DATABASE SCOPED CREDENTIAL,然后创建一个外部数据库源以用于 BULK INSERT 命令。When importing from Azure Blob storage and the data is not public (anonymous access), create a DATABASE SCOPED CREDENTIAL based on a SAS key which is encrypted with a MASTER KEY, and then create an external database source for use in your BULK INSERT command.

使用 BULK INSERTUsing BULK INSERT

下面的示例演示如何使用 BULK INSERT 命令从已创建 SAS 密钥的 Azure Blob 存储位置中的 csv 文件加载数据。The following example shows how to use the BULK INSERT command to load data from a csv file in an Azure Blob storage location on which you have created a SAS key. Azure Blob 存储位置配置为外部数据源。The Azure Blob storage location is configured as an external data source. 这需要使用共享访问签名的数据库范围的凭据,该签名通过用户数据库中的主密钥进行加密。This requires a database scoped credential using a shared access signature that is encrypted using a master key in the user database.

--> Optional - a MASTER KEY is not requred 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 数据库不支持从 Windows 文件读取内容。Azure SQL Database does not support reading from Windows files.

使用 OPENROWSETUsing OPENROWSET

下面的示例演示如何使用 OPENROWSET 命令从已创建 SAS 密钥的 Azure Blob 存储位置中的 csv 文件加载数据。The following example shows how to use the OPENROWSET command to load data from a csv file in an Azure Blob storage location on which you have created a SAS key. Azure Blob 存储位置配置为外部数据源。The Azure Blob storage location is configured as an external data source. 这需要使用共享访问签名的数据库范围的凭据,该签名通过用户数据库中的主密钥进行加密。This requires a database scoped credential using a shared access signature that is encrypted using a master key in the user database.

--> Optional - a MASTER KEY is not requred 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 数据库不支持从 Windows 文件读取内容。Azure SQL Database does not support reading from Windows files.

另请参阅See also