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

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

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

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

Outline

Сохранение значений 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.

Команда	Квалификатор	Тип квалификатора
bcp	-k	Switch
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;

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

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

• Подготовка данных к массовому экспорту или импорту (SQL Server)

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

• Создание файла форматирования (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)
bcp Utility
BULK INSERT (Transact-SQL)
Подсказки таблиц (Transact-SQL)