批量导入数据时保留标识值 (SQL Server)

项目
06/13/2017

可以将包含标识值的数据文件批量导入到 Microsoft SQL Server 的实例中。默认情况下，将忽略导入的数据文件中标识列的值， SQL Server 自动分配唯一值。这些唯一值基于在表创建期间指定的种子和增量值。

如果该数据文件表中的标识符列不包含值，则使用格式化文件来指定导入数据时应跳过表中的标识符列。 SQL Server自动为列分配唯一值。

若要防止 SQL Server 在将数据行大容量导入到表中时分配标识值，请使用相应的保留标识命令限定符。在您指定保留标识限定符后， SQL Server 将在该数据文件中使用标识值。这些限定符如下：

Command	保留标识限定符	限定符类型
`bcp`	-E	开关
BULK INSERT	KEEPIDENTITY	参数
INSERT ... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	表提示

有关详细信息，请参阅 bcp 实用工具、BULK INSERT (Transact-SQL)、OPENROWSET (Transact-SQL)、INSERT (Transact-SQL) 、SELECT (Transact-SQL) 和表提示 (Transact-SQL)。

注意

要创建一个可在多个表中使用的自动递增数字或者可以从应用程序中调用而不引用任何表的自动递增数字，请参阅序列号。

示例

本主题中的示例使用 INSERT ... 批量导入数据SELECT * FROM OPENROWSET (BULK...) 并保留默认值。

示例表

大容量导入实例需要在 dbo 架构下的 AdventureWorks 示例数据库中创建一个名为 myTestKeepNulls 的表。创建该表。在 SQL Server Management Studio 查询编辑器中执行：

USE AdventureWorks;  
GO  
SELECT * INTO HumanResources.myDepartment   
   FROM HumanResources.Department  
      WHERE 1=0;  
GO  
SELECT * FROM HumanResources.myDepartment;

作为 myDepartment 基础的 Department 表的 IDENTITY_INSERT 设置为 OFF。因此，必须指定 KEEPIDENTITY 或 -E，才能将数据导入标识列。

示例数据文件

大容量导入示例中使用的数据文件包含从本机格式的 HumanResources.Department 表中大容量导出的数据。若要创建数据文件，请在 Microsoft Windows 命令提示符处输入：

bcp AdventureWorks.HumanResources.Department out myDepartment-n.Dat -n -T

示例格式化文件

此大容量导入示例使用 XML 格式化文件 myDepartment-f-x-n.Xml，该文件使用本机数据格式。此示例使用 bcp 进行创建，以从 HumanResources.Department 数据库的 AdventureWorks 表生成此格式化文件。在 Windows 命令提示符下，输入以下内容：

bcp AdventureWorks.HumanResources.Department format nul -n -x -f myDepartment-f-n-x.Xml -T

有关创建格式化文件的详细信息，请参阅创建格式化文件 (SQL Server) 。

A. 使用 bcp 并保留标识值

下面的示例说明如何在使用 bcp 大容量导入数据时保留标识值。 bcp 命令使用格式化文件 myDepartment-f-n-x.Xml，并包含下列开关：

限定符	说明
-E	指定标识列使用数据文件中的标识值。
-T	指定`bcp`实用工具使用受信任的连接连接到SQL Server。

在 Windows 命令提示符下输入。

bcp AdventureWorks.HumanResources.myDepartment in C:\myDepartment-n.Dat -f C:\myDepartment-f-n-x.Xml -E -T

B. 使用 BULK INSERT 并保留标识值

下面的示例使用 BULK INSERT 将数据从 myDepartment-c.Dat 文件大容量导入到 AdventureWorks.HumanResources.myDepartment 表中。该语句使用 myDepartment-f-n-x.Xml 格式化文件，并包含 KEEPIDENTITY 选项来确保保留数据文件中的所有标识值。

在SQL Server Management Studio 查询编辑器中，执行：

USE AdventureWorks;  
GO  
DELETE HumanResources.myDepartment;  
GO  
BULK INSERT HumanResources.myDepartment  
   FROM 'C:\myDepartment-n.Dat'  
   WITH (  
      KEEPIDENTITY,  
      FORMATFILE='C:\myDepartment-f-n-x.Xml'  
   );  
GO  
SELECT * FROM HumanResources.myDepartment;

C. 使用 OPENROWSET 并保留标识值

下面的示例使用 OPENROWSET 大容量行集提供程序将数据从 myDepartment-c.Dat 文件大容量导入到 AdventureWorks.HumanResources.myDepartment 表中。该语句使用 myDepartment-f-n-x.Xml 格式化文件，并包含 KEEPIDENTITY 提示来确保保留数据文件中的所有标识值。

在SQL Server Management Studio 查询编辑器中，执行：

USE AdventureWorks;  
GO  
DELETE HumanResources.myDepartment;  
GO  
  
INSERT INTO HumanResources.myDepartment  
   with (KEEPIDENTITY)  
   (DepartmentID, Name, GroupName, ModifiedDate)  
   SELECT *  
      FROM  OPENROWSET(BULK 'C:\myDepartment-n.Dat',  
      FORMATFILE='C:\myDepartment-f-n-x.Xml') as t1;  
GO

Related Tasks

使用格式化文件

使用数据格式进行大容量导入或大容量导出

在使用 bcp 时指定数据格式以获得兼容性

另请参阅

BACKUP (Transact-SQL)
bcp 实用工具
 BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
表提示 (Transact-SQL)