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

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

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

相反，常规 INSERT 语句保留 null 值，而不是插入默认值。 INSERT ...SELECT * FROM OPENROWSET BULK 语句提供与常规 INSERT 相同的基本行为，但另外还支持用于插入默认值的表提示。

保留 null 值

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

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

* 对于 BULK INSERT（Transact-SQL），如果默认值不可用，则必须定义表列以允许 null 值。

注意

这些限定符通过这些批量导入命令禁用对表的定义检查 DEFAULT 。 但是，对于任何并发 INSERT 语句， DEFAULT 应有定义。

将默认值与 INSERT ...SELECT * FROM OPENROWSET BULK

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

有关详细信息，请参阅 OPENROWSET BULK。

示例测试条件

这些示例使用本文中创建的数据库和格式化文件。

将代码示例的本地文件位置更改为计算机上的文件位置。

示例表

该脚本创建一个测试数据库和一个名为 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生成非 XML 格式化文件myNulls.fmt。

• 若要使用 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

有关创建格式化文件的详细信息，请参阅使用 bcp 创建格式化文件（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 并将 null 值与非 XML 格式化文件一起保留

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

对非 XML 格式化文件使用 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 并将 null 值保留为非 XML 格式化文件

参数 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;

对非 XML 格式化文件使用 BULK INSERT 和默认值

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;

使用 OPENROWSET BULK 并将 null 值与非 XML 格式化文件一起保留

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;

使用 OPENROWSET BULK 并使用非 XML 格式化文件保留默认值

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;

相关任务

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

• 准备用于批量导出或导入的数据

使用格式化文件

• 使用 bcp 创建格式化文件（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）
• OPENROWSET BULK （Transact-SQL）
• bcp 实用工具
• BULK INSERT （Transact-SQL）
• 表提示 （Transact-SQL）