在批量导入期间保留 Null 或默认值 (SQL Server)Keep nulls or default values during bulk import (SQL Server)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

默认情况下,将数据导入表中时, bcp 命令和 BULK INSERT 语句将使用为表中的列定义的所有默认值。By default, when data is imported into a table, the bcp command and BULK INSERT statement observe any defaults that are defined for the columns in the table. 例如,如果数据文件中包含一个空字段,则会加载该列的默认值。For example, if there is a null field in a data file, the default value for the column is loaded instead. bcp 命令和 BULK INSERT 语句都允许指定保留 NULL 值。The bcp command and BULK INSERT statement both allow you to specify that nulls values be retained.

相反,常规 INSERT 语句会保留空值而不会插入默认值。In contrast, a regular INSERT statement retains the null value instead of inserting a default value. INSERT ... SELECT * FROM OPENROWSET(BULK...) 语句的基本行为与常规 INSERT 相同,但前者还支持插入默认值的表提示The INSERT ... SELECT * FROM OPENROWSET(BULK...) statement provides the same basic behavior as regular INSERT but additionally supports a table hint for inserting the default values.

轮廓Outline
保留 Null 值Keeping Null Values
对 INSERT 使用默认值...SELECT * FROM OPENROWSET(BULK...)Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)
示例测试条件Example Test Conditions
 ● 示例表 ● Sample Table
 ● 示例数据文件 ● Sample Data File
 ● 示例非 XML 格式化文件 ● Sample Non-XML Format File
在批量导入期间保留 Null 或使用默认值Keep Nulls or Use Default Values During Bulk Import
 ● 在不使用格式化文件的情况下使用 bcp 并保留 NULL 值 ● Using bcp and Keeping Null Values without a Format File
 ● 在使用非 XML 格式化文件的情况下使用 bcp 并保留 Null 值 ● Using bcp and Keeping Null Values with a Non-XML Format File
 ● 在不使用格式化文件的情况下使用 bcp 和默认值 ● Using bcp and Using Default Values without a Format File
 ● 在使用非 XML 格式化文件的情况下使用 bcp 和默认值 ● Using bcp and Using Default Values with a Non-XML Format File
 ● 在不使用格式化文件的情况下使用 BULK INSERT 并保留 Null 值 ● Using BULK INSERT and Keeping Null Values without a Format File
 ● 在使用非 XML 格式化文件的情况下使用 BULK INSERT 并保留 Null 值 ● Using BULK INSERT and Keeping Null Values with a Non-XML Format File
 ● 在不使用格式化文件的情况下使用 BULK INSERT 和默认值 ● Using BULK INSERT and Using Default Values without a Format File
 ● 在使用非 XML 格式化文件的情况下使用 BULK INSERT 和默认值 ● Using BULK INSERT and Using Default Values with a Non-XML Format File
 ● 在使用非 XML 格式化文件的情况下使用 OPENROWSET(BULK...) 并保留 Null 值 ● Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File
 ● 在使用非 XML 格式化文件的情况下使用 OPENROWSET(BULK...) 和默认值 ● Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

保留 Null 值Keeping Null Values

下列限定符指定在大容量导入操作期间数据文件中的空字段保留其空值,而不继承表列的默认值(如果存在)。The following qualifiers specify that an empty field in the data file retains its null value during the bulk-import operation, rather than inheriting a default value (if any) for the table columns. 对于 OPENROWSET,默认情况下,未在批量加载操作中指定的所有列都会设置为 NULL。For OPENROWSET, by default, any columns that are not specified in the bulk-load operation are set to NULL.

CommandCommand QualifierQualifier 限定符类型Qualifier type
bcpbcp -k-k 开关Switch
BULK INSERTBULK INSERT KEEPNULLS*KEEPNULLS* 参数Argument
INSERT ... SELECT * FROM OPENROWSET(BULK...)INSERT ... SELECT * FROM OPENROWSET(BULK...) 空值N/A 空值N/A

* 对于 BULK INSERT,如果默认值不可用,则必须将表列定义为允许 NULL 值。* For BULK INSERT, if default values are not available, the table column must be defined to allow null values.

备注

这些限定符通过这些大容量导入命令禁止检查表上的 DEFAULT 定义。These qualifiers disable checking of DEFAULT definitions on a table by these bulk-import commands. 然而,对于任何并发 INSERT 语句,都需要 DEFAULT 定义。However, for any concurrent INSERT statements, DEFAULT definitions are expected.

对 INSERT 使用默认值...SELECT * FROM OPENROWSET(BULK...)Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)

对于数据文件中的空字段,相应的表列使用其默认值(如果存在)。You can specify that for an empty field in the data file, the corresponding table column uses its default value (if any). 若要使用默认值,请使用表提示 KEEPDEFAULTSTo use default values, use the table hint KEEPDEFAULTS.

示例测试条件Example Test Conditions

本主题中的示例基于下面定义的表、数据文件和格式化文件。The examples in this topic are based on the table, data file, and format file defined below.

示例表Sample Table

下面的脚本创建一个测试数据库和一个名为 myNulls的表。The script below creates a test database and a table named myNulls. 请注意,第四个表列 ( Kids) 具有默认值。Notice that the fourth table column, Kids, has a default value. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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

示例数据文件Sample Data File

使用记事本创建一个空文件 D:\BCP\myNulls.bcp ,并插入下面的数据。Using Notepad, create an empty file D:\BCP\myNulls.bcp and insert the data below. 请注意,在第三条记录(第四列)中没有任何值。Note that there is no value in the third record, fourth column.

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

还可通过执行以下 PowerShell 脚本创建和填充数据文件:Alternatively, you can execute the following PowerShell script to create and populate the data file:

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 格式化文件Sample Non-XML Format File

SQL Server 支持两种类型的格式化文件:非 XML 格式和 XML 格式。SQL Server support two types of format file: non-XML format and XML format. 非 XML 格式是 SQL Server 早期版本支持的原始格式。The non-XML format is the original format that is supported by earlier versions of SQL Server. 有关详细信息,请查看 非 XML 格式化文件 (SQL Server)Please review Non-XML Format Files (SQL Server) for detailed information. 下面的命令基于 的架构使用 bcp 实用工具 myNulls.fmt生成非 XML 格式化文件 myNullsThe following command will use the bcp utility to generate a non-xml format file, myNulls.fmt, based on the schema of myNulls. 若要使用 bcp 命令创建格式化文件,请指定 format 参数,并使用 nul 而不是数据文件路径。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. 格式化选项还需要 -f 选项。The format option also requires the -f option. 此外,对于本示例,限定符 c 用于指定字符数据, t, 用于将逗号指定为 字段终止符,而 T 用于指定使用集成安全性的受信任连接。In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

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

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

重要

确保非 XML 格式化文件以回车符/换行符结尾。Ensure your non-XML format file ends with a carriage return\line feed. 否则可能会收到以下错误消息:Otherwise you will likely receive the following error message:

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

有关创建格式化文件的详细信息,请参阅 创建格式化文件 (SQL Server)For more information about creating format files, see Create a Format File (SQL Server).

在大容量导入期间保留 Null 或使用默认值Keep Nulls or Use Default Values During Bulk Import

下面的示例使用上面创建的数据库、数据文件和格式化文件。The examples below use the database, datafile, and format files created above.

在不使用格式化文件的情况下使用 bcp 并保留 NULL 值Using bcp and Keeping Null Values without a Format File

-k 开关。-k switch. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

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 Using bcp and Keeping Null Values with a Non-XML Format File

-k-f 开关。-k and -f switches. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

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 和默认值Using bcp and Using Default Values without a Format File

在命令提示符处输入以下命令:At a command prompt, enter the following command:

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 Using bcp and Using Default Values with a Non-XML Format File

-f 开关。-f switch. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

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 值Using BULK INSERT and Keeping Null Values without a Format File

KEEPNULLS 参数。KEEPNULLS argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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 Using BULK INSERT and Keeping Null Values with a Non-XML Format File

KEEPNULLSFORMATFILE 参数。KEEPNULLS and the FORMATFILE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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 和默认值Using BULK INSERT and Using Default Values without a Format File

在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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 Using BULK INSERT and Using Default Values with a Non-XML Format File

FORMATFILE 参数。FORMATFILE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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 Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File

FORMATFILE 参数。FORMATFILE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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 Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

KEEPDEFAULTS 表提示和 FORMATFILE 参数。KEEPDEFAULTS table hint and FORMATFILE argument. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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;

使用格式化文件To use a format file

使用数据格式进行大容量导入或大容量导出To use data formats for bulk import or bulk export

在使用 bcp 时指定数据格式以获得兼容性To specify data formats for compatibility when using bcp

另请参阅See Also

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