批量导入数据时保留标识值 (SQL Server)Keep Identity Values When Bulk Importing Data (SQL Server)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

可以将包含标识值的数据文件批量导入到 Microsoft SQL Server 的实例中。Data files that contain identity values can be bulk imported into an instance of Microsoft SQL Server. 默认情况下,将忽略导入的数据文件中标识列的值, SQL ServerSQL Server 自动分配唯一值。By default, the values for the identity column in the data file that is imported are ignored and SQL ServerSQL Server assigns unique values automatically. 这些唯一值基于在表创建期间指定的种子和增量值。The unique values are based on the seed and increment values that are specified during table creation.

如果该数据文件表中的标识符列不包含值,则使用格式化文件来指定导入数据时应跳过表中的标识符列。If the data file does not contain values for the identifier column in the table, use a format file to specify that the identifier column in the table should be skipped when importing data. 有关详细信息,请参阅 使用格式化文件跳过表列 (SQL Server)See Use a Format File to Skip a Table Column (SQL Server) for additional information.

轮廓Outline
保留标识值Keep Identity Values
示例测试条件Example Test Conditions
 ● 示例表 ● Sample Table
 ● 示例数据文件 ● Sample Data File
 ● 示例非 XML 格式化文件 ● Sample Non-XML Format File
示例Examples
 ● 在不使用格式化文件的情况下使用 bcp 并保留标识值 ● Using bcp and Keeping Identity Values without a Format File
 ● 在使用非 XML 格式化文件的情况下使用 bcp 并保留标识值 ● Using bcp and Keeping Identity Values with a Non-XML Format File
 ● 在不使用格式化文件的情况下使用 bcp 和生成的标识值 ● Using bcp and Generated Identity Values without a Format File
 ● 在使用非 XML 格式化文件的情况下使用 bcp 和生成的标识值 ● Using bcp and Generated Identity Values with a Non-XML Format File
 ● 在不使用格式化文件的情况下使用 BULK INSERT 并保留标识值 ● Using BULK INSERT and Keeping Identity Values without a Format File
 ● 在使用非 XML 格式化文件的情况下使用 BULK INSERT 并保留标识值 ● Using BULK INSERT and Keeping Identity Values with a Non-XML Format File
 ● 在不使用格式化文件的情况下使用 BULK INSERT 和生成的标识值 ● Using BULK INSERT and Generated Identity Values without a Format File
 ● 在使用非 XML 格式化文件的情况下使用 BULK INSERT 和生成的标识值 ● Using BULK INSERT and Generated Identity Values with a Non-XML Format File
 ● 在使用非 XML 格式化文件的情况下使用 OPENROWSET 并保留标识值 ● Using OPENROWSET and Keeping Identity Values with a Non-XML Format File
 ● 在使用非 XML 格式化文件的情况下使用 OPENROWSET 和生成的标识值 ● Using OPENROWSET and Generated Identity Values with a Non-XML Format File

保留标识值 Keep Identity Values

若要防止 SQL ServerSQL Server 在将数据行大容量导入到表中时分配标识值,请使用相应的保留标识命令限定符。To prevent SQL ServerSQL Server from assigning identity values while bulk importing data rows into a table, use the appropriate keep-identity command qualifier. 在您指定保留标识限定符后, SQL ServerSQL Server 将在该数据文件中使用标识值。When you specify a keep-identity qualifier, SQL ServerSQL Server uses the identity values in the data file. 这些限定符如下:These qualifiers are as follows:

CommandCommand 保留标识限定符Keep-identity qualifier 限定符类型Qualifier type
bcpbcp -E-E 开关Switch
BULK INSERTBULK INSERT KEEPIDENTITYKEEPIDENTITY 参数Argument
INSERT ... SELECT * FROM OPENROWSET(BULK...)INSERT ... SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITYKEEPIDENTITY 表提示Table hint

有关详细信息,请参阅 bcp 实用工具BULK INSERT (Transact-SQL)OPENROWSET (Transact-SQL)INSERT (Transact-SQL)SELECT (Transact-SQL)表提示 (Transact-SQL)For more information, see bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), and Table Hints (Transact-SQL).

备注

要创建一个可在多个表中使用的自动递增数字或者可以从应用程序中调用而不引用任何表的自动递增数字,请参阅 序列号To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.

示例测试条件Example Test Conditions

本主题中的示例基于下面定义的表、数据文件和格式化文件。The examples in this topic are based on the table, data file, and format file defined below.

示例表Sample Table

下面的脚本创建一个测试数据库和一个名为 myIdentity的表。The script below creates a test database and a table named myIdentity. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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
   );

示例数据文件Sample Data File

使用记事本创建一个空文件 D:\BCP\myIdentity.bcp ,并插入下面的数据。Using Notepad, create an empty file D:\BCP\myIdentity.bcp and insert the data below.

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 脚本创建和填充数据文件:Alternatively, you can execute the following PowerShell script to create and populate the data file:

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 格式化文件Sample Non-XML Format File

SQL Server 支持两种类型的格式化文件:非 XML 格式和 XML 格式。SQL Server support two types of format file: non-XML format and XML format. 非 XML 格式是 SQL Server 早期版本支持的原始格式。The non-XML format is the original format that is supported by earlier versions of SQL Server. 有关详细信息,请查看 非 XML 格式化文件 (SQL Server)Please review Non-XML Format Files (SQL Server) for detailed information. 下面的命令基于 的架构使用 bcp 实用工具 myIdentity.fmt生成非 XML 格式化文件 myIdentityThe following command will use the bcp utility to generate a non-xml format file, myIdentity.fmt, based on the schema of myIdentity. 若要使用 bcp 命令创建格式化文件,请指定 format 参数,并使用 nul 而不是数据文件路径。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. 格式化选项还需要 -f 选项。The format option also requires the -f option. 此外,对于本示例,限定符 c 用于指定字符数据, t, 用于将逗号指定为 字段终止符,而 T 用于指定使用集成安全性的受信任连接。In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

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

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

重要

确保非 XML 格式化文件以回车符/换行符结尾。Ensure your non-XML format file ends with a carriage return\line feed. 否则可能会收到以下错误消息:Otherwise you will likely receive the following error message:

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

示例Examples

下面的示例使用上面创建的数据库、数据文件和格式化文件。The examples below use the database, datafile, and format files created above.

在不使用格式化文件的情况下使用 bcp 并保留标识值Using bcp and Keeping Identity Values without a Format File

-E 开关。-E switch. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

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;"

Using bcp and Keeping Identity Values with a Non-XML Format FileUsing bcp and Keeping Identity Values with a Non-XML Format File

-E-f 开关。-E and -f switches. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

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 和生成的标识值Using bcp and Generated Identity Values without a Format File

使用默认值。Using defaults. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

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;"

Using bcp and Generated Identity Values with a Non-XML Format FileUsing bcp and Generated Identity Values with a Non-XML Format File

使用默认值和 -f 开关。Using defaults and -f switch. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

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 并保留标识值Using BULK INSERT and Keeping Identity Values without a Format File

KEEPIDENTITY 参数。KEEPIDENTITY argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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;

Using BULK INSERT and Keeping Identity Values with a Non-XML Format FileUsing BULK INSERT and Keeping Identity Values with a Non-XML Format File

KEEPIDENTITYFORMATFILE 参数。KEEPIDENTITY and the FORMATFILE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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 和生成的标识值Using BULK INSERT and Generated Identity Values without a Format File

使用默认值。Using defaults. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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;

Using BULK INSERT and Generated Identity Values with a Non-XML Format FileUsing BULK INSERT and Generated Identity Values with a Non-XML Format File

使用默认值和 FORMATFILE 参数。Using defaults and FORMATFILE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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;

Using OPENROWSET(BULK...) and Keeping Identity Values with a Non-XML Format FileUsing OPENROWSET(BULK...) and Keeping Identity Values with a Non-XML Format File

KEEPIDENTITY 表提示和 FORMATFILE 参数。KEEPIDENTITY table hint and FORMATFILE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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;

Using OPENROWSET(BULK...) and Generated Identity Values with a Non-XML Format FileUsing OPENROWSET(BULK...) and Generated Identity Values with a Non-XML Format File

使用默认值和 FORMATFILE 参数。Using defaults and FORMATFILE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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;

相关任务Related Tasks

使用格式化文件To use a format file

使用数据格式进行大容量导入或大容量导出To use data formats for bulk import or bulk export

在使用 bcp 时指定数据格式以获得兼容性To specify data formats for compatibility when using bcp

  1. 指定字段终止符和行终止符 (SQL Server)Specify Field and Row Terminators (SQL Server)

  2. 使用 bcp 指定数据文件中的前缀长度 (SQL Server)Specify Prefix Length in Data Files by Using bcp (SQL Server)

  3. 使用 bcp 指定文件存储类型 (SQL Server)Specify File Storage Type by Using bcp (SQL Server)

另请参阅See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
bcp 实用工具 bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
表提示 (Transact-SQL)Table Hints (Transact-SQL)
用来导入或导出数据的格式化文件 (SQL Server)Format Files for Importing or Exporting Data (SQL Server)