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

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

默认情况下，将数据导入表中时， bcp 命令和 BULK INSERT 语句将使用为表中的列定义的所有默认值。 例如，如果数据文件中包含一个空字段，则会加载该列的默认值。 bcp 命令和 BULK INSERT 语句都允许指定保留 NULL 值。

相反，常规 INSERT 语句会保留空值而不会插入默认值。 INSERT ... SELECT * FROM OPENROWSET(BULK...) 语句的基本行为与常规 INSERT 相同，但前者还支持插入默认值的表提示。

轮廓

保留 Null 值 对 INSERT 使用默认值...SELECT * FROM OPENROWSET(BULK...) 示例测试条件  ● 示例表  ● 示例数据文件  ● 示例非 XML 格式化文件 在批量导入期间保留 Null 或使用默认值  ● 在不使用格式化文件的情况下使用 bcp 并保留 Null 值  ● 在使用非 XML 格式化文件的情况下使用 bcp 并保留 Null 值  ● 在不使用格式化文件的情况下使用 bcp 和默认值  ● 在使用非 XML 格式化文件的情况下使用 bcp 和默认值  ● 在不使用格式化文件的情况下使用 BULK INSERT 并保留 Null 值  ● 在使用非 XML 格式化文件的情况下使用 BULK INSERT 并保留 Null 值  ● 在不使用格式化文件的情况下使用 BULK INSERT 和默认值  ● 在使用非 XML 格式化文件的情况下使用 BULK INSERT 和默认值  ● 在使用非 XML 格式化文件的情况下使用 OPENROWSET(BULK...) 并保留 Null 值  ● 在使用非 XML 格式化文件的情况下使用 OPENROWSET(BULK...) 和默认值

保留 Null 值

下列限定符指定在大容量导入操作期间数据文件中的空字段保留其空值，而不继承表列的默认值（如果存在）。 对于 OPENROWSET，默认情况下，未在批量加载操作中指定的所有列都会设置为 NULL。

Command	Qualifier	限定符类型
bcp	-k	开关
BULK INSERT	KEEPNULLS*	参数
INSERT ... SELECT * FROM OPENROWSET(BULK...)	空值	空值

*对于 BULK INSERT，如果默认值不可用，则必须将表列定义为允许 null 值。

注意

这些限定符通过这些大容量导入命令禁止检查表上的 DEFAULT 定义。 然而，对于任何并发 INSERT 语句，都需要 DEFAULT 定义。

对 INSERT 使用默认值...SELECT * FROM OPENROWSET(BULK...)

对于数据文件中的空字段，相应的表列使用其默认值（如果存在）。 若要使用默认值，请使用表提示 KEEPDEFAULTS。

注意

有关详细信息，请参阅 INSERT (Transact-SQL)、SELECT (Transact-SQL)、OPENROWSET (Transact-SQL) 和 Table Hints (Transact-SQL)

示例测试条件

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

示例表

下面的脚本创建一个测试数据库和一个名为 myNulls的表。 请注意，第四个表列 ( Kids) 具有默认值。 在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNulls ( 
   PersonID smallint not null,
   FirstName varchar(25),
   LastName varchar(30),
   Kids varchar(13) DEFAULT 'Default Value',
   BirthDate date
   );

示例数据文件

使用记事本创建一个空文件 D:\BCP\myNulls.bcp ，并插入下面的数据。 请注意，在第三条记录（第四列）中没有任何值。

1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,1963-11-14
3,Stella,Rosenhain,,1992-03-02

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

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

$bcpFile = $dir + 'MyNulls.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 '1,Anthony,Grosse,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';

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

示例非 XML 格式化文件

SQL Server 支持两种类型的格式化文件：非 XML 格式和 XML 格式。 非 XML 格式是 SQL Server 早期版本支持的原始格式。 有关详细信息，请查看 非 XML 格式化文件 (SQL Server) 。 下面的命令基于 的架构使用 bcp 实用工具 myNulls.fmt生成非 XML 格式化文件 myNulls。 若要使用 bcp 命令创建格式化文件，请指定 format 参数，并使用 nul 而不是数据文件路径。 格式化选项还需要 -f 选项。 此外，对于本示例，限定符 c 用于指定字符数据， t, 用于将逗号指定为 字段终止符，而 T 用于指定使用集成安全性的受信任连接。 在命令提示符处输入以下命令：

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

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

重要

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

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

有关创建格式化文件的详细信息，请参阅 创建格式化文件 (SQL Server)。

在大容量导入期间保留 Null 或使用默认值

下面的示例使用上面创建的数据库、数据文件和格式化文件。

在不使用格式化文件的情况下使用 bcp 并保留 NULL 值

-k 开关。 在命令提示符处输入以下命令：

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

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

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

在不使用格式化文件的情况下使用 bcp 的情况下使用 bcp

-k 和 -f 开关。 在命令提示符处输入以下命令：

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

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

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

在不使用格式化文件的情况下使用 bcp 和默认值

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

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

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

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

在不使用格式化文件的情况下使用 bcp 的情况下使用 bcp

-f 开关。 在命令提示符处输入以下命令：

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

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

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

在不使用格式化文件的情况下使用 BULK INSERT 并保留 Null 值

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

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

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

在不使用格式化文件的情况下使用 BULK INSERT 的情况下使用 bcp

KEEPNULLS 和 FORMATFILE 参数。 在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

USE TestDatabase;
GO

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

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

在不使用格式化文件的情况下使用 BULK INSERT 和默认值

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

USE TestDatabase;
GO

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

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

在不使用格式化文件的情况下使用 BULK INSERT 的情况下使用 bcp

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

USE TestDatabase;
GO

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

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

在不使用格式化文件的情况下使用 FROM OPENROWSET(BULK...) 的情况下使用 bcp

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

USE TestDatabase;
GO

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

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

在不使用格式化文件的情况下使用 FROM OPENROWSET(BULK...) 的情况下使用 bcp

KEEPDEFAULTS 表提示和 FORMATFILE 参数。 在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
WITH (KEEPDEFAULTS) 
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myNulls.bcp', 
		FORMATFILE = 'D:\BCP\myNulls.fmt'  
		) AS t1;

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

Related Tasks

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

• 准备用于批量导出或导入的数据 (SQL Server)

使用格式化文件

• 创建格式化文件 (SQL Server)

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

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

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

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

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

• 导入来自早期版本的 SQL Server 的本机格式数据和字符格式数据

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

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

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

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

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

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

• 使用 bcp 指定数据文件中的前缀长度 (SQL Server)

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

另请参阅

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