使用BULK INSERT或 OPENROWSET（BULK...）将数据导入SQL Server

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

本文概述了如何使用 Transact-SQL BULK INSERT 语句和 INSERT...SELECT * FROM OPENROWSET(BULK...) 语句将数据从数据文件批量导入 SQL Server 或 Azure SQL 数据库表。

本文还介绍了使用 BULK INSERT 和 OPENROWSET(BULK...)使用这些方法从远程数据源批量导入的安全注意事项。

BULK INSERT 语句

BULK INSERT 将数据从数据文件加载到表中。 该功能与in命令的bcp选项提供的功能类似。 但是，SQL Server 进程读取数据文件。 有关语法的说明BULK INSERT，请参阅BULK INSERT（Transact-SQL）。

BULK INSERT 示例

• BULK INSERT （Transact-SQL）
• 批量导入和导出 XML 文档的示例 (SQL Server)
• 批量导入数据时保留标识值 (SQL Server)
• 在批量导入期间保留 Null 或默认值 (SQL Server)
• 指定字段和行终止符（SQL Server）
• 使用格式文件批量导入数据 (SQL Server)
• 使用字符格式导入或导出数据 (SQL Server)
• 使用本机格式导入或导出数据 (SQL Server)
• 使用 Unicode 字符格式导入或导出数据 (SQL Server)
• 使用 Unicode 本机格式导入或导出数据 (SQL Server)
• 使用格式化文件跳过表列 (SQL Server)
• 使用格式文件将表列映射到数据文件字段 (SQL Server)

OPENROWSET(BULK...) 函数

通过调用 OPENROWSET 函数并指定 OPENROWSET 选项，访问 BULK 大容量行集提供程序。 此 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...） 语句 - 示例

• 批量导入和导出 XML 文档的示例 (SQL Server)
• 批量导入数据时保留标识值 (SQL Server)
• 在批量导入期间保留 Null 或默认值 (SQL Server)
• 使用格式文件批量导入数据 (SQL Server)
• 使用字符格式导入或导出数据 (SQL Server)
• 使用格式化文件跳过表列 (SQL Server)
• 使用格式文件跳过数据字段 (SQL Server)
• 使用格式文件将表列映射到数据文件字段 (SQL Server)

安全注意事项

如果用户使用的是 SQL Server 登录名，则系统将使用 SQL Server 进程帐户的安全配置文件。 采用 SQL Server 身份验证的登录名无法在数据库引擎外部通过身份验证。 因此，当 BULK INSERT 命令由使用 SQL Server 身份验证的登录名启动时，使用 SQL Server 进程帐户（SQL Server 数据库引擎服务使用的帐户）的安全上下文建立到数据的连接。

要成功读取源数据，你必须授予 SQL Server 数据库引擎使用的帐户访问源数据的权限。 与此相反，如果 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上的数据文件的访问权限，而且凭据委托已设置妥当，则用户可以连接到运行在 SQL Server Computer_C 上的实例，访问 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';

从 Azure Blob 存储批量导入

从 Azure Blob 存储导入数据时，如果数据不是公开的（匿名访问），请基于使用 CREATE MASTER KEY 加密的 SAS 密钥创建 CREATE DATABASE SCOPED CREDENTIAL，然后创建一个外部数据库源供 BULK INSERT 命令使用。

或者，基于MANAGED IDENTITY创建一个CREATE DATABASE SCOPED CREDENTIAL，以授权对非公共存储帐户中数据的访问请求。 使用 MANAGED IDENTITY 时，Azure 存储必须通过添加“存储 Blob 数据参与者”内置 Azure 基于角色的访问控制 (RBAC) 角色来授予对实例的托管标识的权限，该角色为必要的Azure Blob 存储容器提供对托管标识的读/写访问权限。 Azure SQL 托管实例具有系统分配的托管标识，还可以具有一个或多个用户分配的托管标识。 可以使用系统分配的托管标识或用户分配的托管标识来授权请求。 对于授权，将使用托管实例的 default 标识（即主要用户分配的托管标识，如果未指定用户分配的托管标识，则为系统分配的托管标识）。

使用 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');

使用 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;

相关内容

• INSERT （Transact-SQL）
• SELECT 子句 (Transact-SQL)
• 大容量导入和导出数据 (SQL Server)
• OPENROWSET （Transact-SQL）
• SELECT （Transact-SQL）
• FROM 子句以及 JOIN、APPLY、PIVOT (Transact-SQL)
• bcp 实用工具
• BULK INSERT （Transact-SQL）