Сохранение значений NULL или значений по умолчанию при массовом импорте данных (SQL Server)

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureАналитика Synapse AzureСистема аналитической платформы (PDW)

При импорте данных в таблицу команда bcp и инструкция BULK INSERT используют значения по умолчанию, которые определены для столбцов таблицы. Например, если поле в файле данных имеет значение NULL, вместо него загружается значение по умолчанию соответствующего столбца. И команда bcp , и инструкция BULK INSERT позволяют пользователю указать, следует ли оставлять значения NULL.

В отличие от этого, обычная INSERT инструкция сохраняет значение NULL вместо вставки значения по умолчанию. Инструкция INSERT ... SELECT * FROM OPENROWSET BULK обеспечивает то же базовое поведение, что и обычной инструкции INSERT, но также поддерживает подсказку таблицы для вставки значений по умолчанию.

Сохранение значений NULL

Следующие квалификаторы указывают, что пустое поле в файле данных сохраняет свое значение 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таблицы имеет значение по умолчанию. Выполните следующий код Transact-SQL в Microsoft SQL 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
   );

Пример файла данных

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

Дополнительные сведения о создании файлов форматирования см. в статье "Создание файла форматирования с помощью 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;"

Использование BCP и значений по умолчанию с файлом формата, не являющимся XML.

Переключатель -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.

Выполните следующий код Transact-SQL в Microsoft SQL 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.

Аргумент KEEPNULLS и аргумент FORMATFILE.

Выполните следующий код Transact-SQL в Microsoft SQL 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 и использование значений по умолчанию без файла форматирования

Выполните следующий код Transact-SQL в Microsoft SQL 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.

Аргумент FORMATFILE.

Выполните следующий код Transact-SQL в Microsoft SQL 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

Аргумент FORMATFILE.

Выполните следующий код Transact-SQL в Microsoft SQL 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

Подсказка KEEPDEFAULTS для таблицы и аргумент FORMATFILE.

Выполните следующий код Transact-SQL в Microsoft SQL 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;

Связанные задачи

• Сохранение значений идентификаторов при массовом импорте данных (SQL Server)

• Подготовка данных для массового экспорта или импорта

Использование файла форматирования

• Создание файла форматирования с помощью bcp (SQL Server)

• Использование файла форматирования для массового импорта данных (SQL Server)

• Использование файла форматирования для сопоставления столбцов таблиц с полями файлов данных (SQL Server)

• Использование файла форматирования для пропуска поля данных (SQL Server)

• Использование файла форматирования для пропуска столбца таблицы (SQL Server)

Использование форматов данных для массового импорта или экспорта

• Импорт данных собственного и символьного формата из более ранних версий SQL Server

• Использование формата символов для импорта или экспорта данных (SQL Server)

• Использование собственного формата для импорта или экспорта данных (SQL Server)

• Использование формата символов Юникода для импорта или экспорта данных (SQL Server)

• Использование собственного формата Юникода для импорта или экспорта данных (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)