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

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

可以将包含标识值的数据文件批量导入到 Microsoft SQL Server 的实例中。

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

如果该数据文件表中的标识符列不包含值，则使用格式化文件来指定导入数据时应跳过表中的标识符列。 有关详细信息，请参阅 使用格式化文件跳过表列 (SQL Server) 。

保留标识值

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

这些限定符如下：

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

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

注意

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

示例测试条件

本主题中的示例基于下面定义的表、数据文件和格式化文件。

示例表

下面的脚本创建一个测试数据库和一个名为 myIdentity的表。 在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myIdentity ( 
   PersonID smallint IDENTITY(1,1) NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date
   );

示例数据文件

使用记事本创建一个空文件 D:\BCP\myIdentity.bcp ，并插入下面的数据。

3,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
1,Stella,Rosenhain,1992-03-02
4,Miller,Dylan,1954-01-05

还可通过执行以下 PowerShell 脚本创建和填充数据文件：

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'myIdentity.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '3,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '1,Stella,Rosenhain,1992-03-02';
Add-Content -Path $bcpFile -Value '4,Miller,Dylan,1954-01-05';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

非 XML 格式化文件示例

SQL Server 支持两种类型的格式化文件：非 XML 格式和 XML 格式。 非 XML 格式是 SQL Server 早期版本支持的原始格式。 有关详细信息，请参阅使用非 XML 格式化文件（SQL Server）。

下面的命令基于 的架构使用 bcp 实用工具 myIdentity.fmt生成非 XML 格式化文件 myIdentity。

• 若要使用 bcp 命令创建格式化文件，请指定 format 参数并使用 nul 而不是数据文件路径。
• 格式选项还需要使用 -f 选项。
• c 用于指定字符数据
• t, 用于将逗号指定为 字段终止符
• T 用于使用集成安全性指定受信任的连接。

在命令提示符处输入以下命令：

bcp TestDatabase.dbo.myIdentity format nul -c -f D:\BCP\myIdentity.fmt -t, -T

REM Review file
Notepad D:\BCP\myIdentity.fmt

重要

确保非 XML 格式化文件以回车符/换行符结尾。 否则可能会收到以下错误消息：

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

例子

这些示例使用本文中创建的数据库、数据文件和格式化文件。

使用 bcp 并保留没有格式化文件的标识值

开关 -E 。

在命令提示符处输入以下命令：

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t, -E

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

使用 bcp 并将标识值保留为非 XML 格式化文件

-E和-f开关。

在命令提示符处输入以下命令：

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T -E

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

在不使用格式化文件的情况下使用 bcp 和生成的标识值

使用默认值。

在命令提示符处输入以下命令：

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t,

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

将 bcp 和生成的标识值与非 XML 格式化文件配合使用

使用默认值和 -f 切换。

在命令提示符处输入以下命令：

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

使用 BULK INSERT 并保留没有格式化文件的标识值

KEEPIDENTITY 参数。

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
    FROM 'D:\BCP\myIdentity.bcp'
    WITH (
        DATAFILETYPE = 'char',  
        FIELDTERMINATOR = ',',  
        KEEPIDENTITY
        );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

使用 BULK INSERT 并将标识值保留为非 XML 格式化文件

参数 KEEPIDENTITY 和 FORMATFILE 参数。

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myIdentity.fmt',
        KEEPIDENTITY
        );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

在不使用格式化文件的情况下使用 BULK INSERT 和生成的标识值

使用默认值。

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
      );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

将 BULK INSERT 和生成的标识值与非 XML 格式化文件配合使用

使用默认值和 FORMATFILE 参数。

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myIdentity.fmt'
        );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

使用 OPENROWSET BULK 并将标识值保留为非 XML 格式化文件

KEEPIDENTITY表提示和FORMATFILE参数。

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
WITH (KEEPIDENTITY) 
(PersonID, FirstName, LastName, BirthDate)
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

将 OPENROWSET BULK 和生成的标识值与非 XML 格式化文件配合使用

使用默认值和 FORMATFILE 参数。

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
(FirstName, LastName, BirthDate)
    SELECT FirstName, LastName, BirthDate
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

相关任务

• 在批量导入期间保留 Null 或默认值 (SQL Server)

• 准备用于批量导出或导入的数据

使用格式化文件

• 使用 bcp 创建格式化文件（SQL Server）

• 使用格式文件批量导入数据 (SQL Server)

• 使用格式文件将表列映射到数据文件字段 (SQL Server)

• 使用格式文件跳过数据字段 (SQL Server)

• 使用格式化文件跳过表列 (SQL Server)

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

• 从早期版本的 SQL Server 导入本地格式和字符格式数据

• 使用字符格式导入或导出数据 (SQL Server)

• 使用本机格式导入或导出数据 (SQL Server)

• 使用 Unicode 字符格式导入或导出数据 (SQL Server)

• 使用 Unicode 本机格式导入或导出数据 (SQL Server)

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

1. 指定字段和行终止符（SQL Server）

2. 使用 bcp 在数据文件中指定前缀长度（SQL Server）

3. 使用 bcp 指定文件存储类型（SQL Server）

相关内容

• BACKUP （Transact-SQL）
• bcp 实用工具
• BULK INSERT （Transact-SQL）
• OPENROWSET （Transact-SQL）
• 表提示 （Transact-SQL）
• 格式化文件以导入或导出数据（SQL Server）