批量导入数据时保留标识值（SQL Server）

2017-06-13

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

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

若要防止 SQL Server 在将数据行批量导入表中时分配标识值，请使用适当的 keep-identity 命令限定符。指定 keep-identity 限定符时，SQL Server 将使用数据文件中的标识值。这些限定符如下所示：

指令	保持身份限定符	限定符类型
`bcp`	-E	开关
批量插入	KEEPIDENTITY	论点
插入……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“部门”表已将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）。

答：使用 bcp 和保留标识值

以下示例演示如何在使用 bcp 批量导入数据时保留标识值。该 bcp 命令使用格式化文件myDepartment-f-n-x.Xml，并包含以下开关：

限定符	DESCRIPTION
-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

另请参阅

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

通过