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

  • Статья

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse Analytics AnalyticsPlatform System (PDW)

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

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

Контур
Сохранение значений NULL
Использование значений по умолчанию при выполнении инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...)
Пример условий теста
 ● Пример таблицы
 ● Пример файла данных
 ● Пример файла форматирования, отличного от XML
Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных
 ● Использование bcp и сохранение значений NULL без файла форматирования
 ● Использование bcp и сохранение значений NULL в файле форматирования, отличном от XML
 ● Использование bcp и значений по умолчанию без файла форматирования
 ● Использование bcp и значений по умолчанию с файлом форматирования, не являющимся XML
 ● Использование BULK INSERT и сохранение значений NULL без файла форматирования
 ● Использование ИНСТРУКЦИИ BULK INSERT и сохранение значений NULL в файле форматирования, отличном от XML
 ● Использование BULK INSERT и значений по умолчанию без файла форматирования
 ● Использование инструкции BULK INSERT и значений по умолчанию с файлом форматирования, не являющимся XML
 ● Использование OPENROWSET(BULK...) и сохранение значений NULL в файле форматирования, отличном от XML
 ● Использование OPENROWSET(BULK...) и значений по умолчанию с файлом форматирования, отличного от XML

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

Следующие квалификаторы указывают, что вместо пустого поля в файле данных необходимо вставить не значение по умолчанию, а значение NULL. Для инструкции OPENROWSETпо умолчанию любым столбцам, не участвующим в операции массовой загрузки, присваивается значение NULL.

Get-Help Квалификатор Тип квалификатора
bcp -k Параметр
BULK INSERT KEEPNULLS* Аргумент
Инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...). Недоступно Н/Д

* Если значения по умолчанию недоступны для BULK INSERT, необходимо определить столбец таблицы, чтобы разрешить значения NULL.

Примечание

Эти квалификаторы отключают проверку определений DEFAULT для таблицы командами массового импорта. Однако для одновременно выполняемых инструкций INSERT определения DEFAULT требуются.

Использование значений по умолчанию с помощью инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...)

Можно указать, что вместо пустых полей в файле данных необходимо вставить значения по умолчанию соответствующих столбцов (если они заданы). Чтобы использовать значения по умолчанию, используйте табличную подсказку KEEPDEFAULTS.

Примечание

Дополнительные сведения см. в разделах INSERT (Transact-SQL),SELECT (Transact-SQL),OPENROWSET (Transact-SQL) и Табличные указания (Transact-SQL).

Пример условий теста

Примеры в этом разделе основаны на таблице, файле данных и файле форматирования, которые определены ниже.

Образец таблицы

Приведенный ниже сценарий создает тестовую базу данных и таблицу с именем 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 для создания файла форматирования 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

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

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

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

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

Задание форматов данных для совместимости с помощью программы bcp

См. также:

BACKUP (Transact-SQL)
OPENROWSET (Transact-SQL)
Программа bcp
BULK INSERT (Transact-SQL)
Табличные указания (Transact-SQL)