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

适用于:SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics

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

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

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

保留标识值

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

这些限定符如下:

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

有关详细信息,请参阅 bcp Utility、 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:

注意

当你在 Azure SQL 数据库 中执行CREATE DATABASE且不带其他选项时,数据库会在逻辑服务器上以默认设置创建。 在 Azure SQL 数据库或 Microsoft Fabric SQL 数据库中,如果目标数据库已存在,你可以跳过示例中的 CREATE DATABASE 和不受支持的 USE 语句,直接连接到该数据库。

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 而不是数据文件路径。
  • format 选项还要求使用 -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;"

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

使用默认值和 -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:

注意

在 Azure SQL 数据库 和 Microsoft Fabric 中的 SQL 数据库中,该USE语句不可用。 相反,直接连接到目标数据库,运行剩余的代码样本。

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;

在使用非 XML 格式化文件的情况下使用 OPENROWSET 和生成的标识值

使用默认值和 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;

使用格式化文件

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

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

  1. 指定字段和行终止符(SQL Server)

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

  3. 使用 bcp 指定文件存储类型(SQL Server)