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

  • 项目

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

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

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

轮廓
保留标识值
示例测试条件
 ● 示例表
 ● 示例数据文件
 ● 非 XML 格式示例文件
示例
 ● 在不使用格式化文件的情况下使用 bcp 并保留标识值
 ● 在使用非 XML 格式化文件的情况下使用 bcp 并保留标识值
 ● 在不使用格式化文件的情况下使用 bcp 和生成的标识值
 ● 在使用非 XML 格式化文件的情况下使用 bcp 和生成的标识值
 ● 在不使用格式化文件的情况下使用 BULK INSERT 并保留标识值
 ● 在使用非 XML 格式化文件的情况下使用 BULK INSERT 并保留标识值
 ● 在不使用格式化文件的情况下使用 BULK INSERT 和生成的标识值
 ● 在使用非 XML 格式化文件的情况下使用 BULK INSERT 和生成的标识值
 ● 在使用非 XML 格式化文件的情况下使用 OPENROWSET 并保留标识值
 ● 在使用非 XML 格式化文件的情况下使用 OPENROWSET 和生成的标识值

保留标识值

若要防止 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)。

注意

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

示例测试条件

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

示例表

下面的脚本创建一个测试数据库和一个名为 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;"

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

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

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

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

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

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;

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

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

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

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;

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

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

Related Tasks

使用格式化文件

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

在使用 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)