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

适用于:SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics

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

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

保留空值

下列限定符指定在大容量导入操作期间数据文件中的空字段保留其空值,而不继承表列的默认值(如果存在)。 对于 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:

注意

当你在 Azure SQL 数据库 中执行CREATE DATABASE且不带其他选项时,数据库会在逻辑服务器上以默认设置创建。 在 Azure SQL 数据库或 Microsoft Fabric SQL 数据库中,如果目标数据库已存在,你可以跳过示例中的 CREATE DATABASE 和不受支持的 USE 语句,直接连接到该数据库。

USE [master];
GO
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 而不是数据文件路径。
  • format 选项还要求使用 -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:

注意

在 Azure SQL 数据库 和 Microsoft Fabric 中的 SQL 数据库中,该USE语句不可用。 相反,直接连接到目标数据库,运行剩余的代码样本。

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;

在非 XML 格式化文件中使用 BULK INSERT 并保留 null 值

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

使用格式化文件

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

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