Сохранение значений NULL или значений по умолчанию при массовом импорте данных (SQL Server)Keep nulls or default values during bulk import (SQL Server)

ОБЛАСТЬ ПРИМЕНЕНИЯ: ДаSQL Server ДаБаза данных SQL Azure ДаAzure Synapse Analytics (Хранилище данных SQL) ДаParallel Data Warehouse APPLIES TO: YesSQL Server YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) YesParallel 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. Например, если поле в файле данных имеет значение NULL, вместо него загружается значение по умолчанию соответствующего столбца.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, напротив, оставляет значение NULL и не использует значения по умолчанию.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
Сохранение значений NULLKeeping 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
 ● Использование команды bcp и сохранение значений NULL с помощью файла форматирования в формате, отличном от XML ● Using bcp and Keeping Null Values with a Non-XML Format File
 ● Использование команды bcp и значений по умолчанию без файла форматирования ● Using bcp and Using Default Values without a Format File
 ● Использование команды bcp и значений по умолчанию с помощью файла форматирования в формате, отличном от XML ● 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
 ● Использование инструкции BULK INSERT и сохранение значений NULL с помощью файла форматирования в формате, отличном от XML ● 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
 ● Использование инструкции BULK INSERT и значений по умолчанию с помощью файла форматирования в формате, отличном от XML ● Using BULK INSERT and Using Default Values with a Non-XML Format File
 ● Использование инструкции OPENROWSET(BULK...) и сохранение значений NULL с помощью файла форматирования в формате, отличном от XML ● Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File
 ● Использование инструкции OPENROWSET(BULK...) и значений по умолчанию с помощью файла форматирования в формате, отличном от XML ● Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Сохранение значений NULLKeeping Null Values

Следующие квалификаторы указывают, что вместо пустого поля в файле данных необходимо вставить не значение по умолчанию, а значение NULL.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.

Get-HelpCommand КвалификаторQualifier Тип квалификатора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). Чтобы использовать значения по умолчанию, используйте табличную подсказку KEEPDEFAULTS.To 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. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):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;

Образец файла форматирования в формате, отличном от XMLSample 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, на основе схемы myNulls.The 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. Параметр format также требует наличия параметра -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 и сохранение значений NULL с помощью файла форматирования в формате, отличном от XML 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 и значений по умолчанию с помощью файла форматирования в формате, отличном от XML 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. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):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 и сохранение значений NULL с помощью файла форматирования в формате, отличном от XML Using BULK INSERT and Keeping Null Values with a Non-XML Format File

KEEPNULLS и аргумент FORMATFILE .KEEPNULLS and the FORMATFILE argument. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):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

Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):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 и значений по умолчанию с помощью файла форматирования в формате, отличном от XML Using BULK INSERT and Using Default Values with a Non-XML Format File

АргументFORMATFILE .FORMATFILE argument. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):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;

Использование инструкции OPENROWSET(BULK...) и сохранение значений NULL с помощью файла форматирования в формате, отличном от XML Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File

АргументFORMATFILE .FORMATFILE argument. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):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;

Использование инструкции OPENROWSET(BULK...) и значений по умолчанию с помощью файла форматирования в формате, отличном от XML Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File

Табличное указаниеKEEPDEFAULTS и аргумент FORMATFILE .KEEPDEFAULTS table hint and FORMATFILE argument. Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):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

Задание форматов данных для совместимости с помощью программы bcpTo specify data formats for compatibility when using bcp

См. также:See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
Табличные указания (Transact-SQL)Table Hints (Transact-SQL)