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

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics 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

使用格式化文件

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

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

另请参阅

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