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

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

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

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

Образец таблицы и файла данных

Для выполнения примеров этого подраздела необходимо создать образец таблицы и файла данных.

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

Для выполнения примеров этого раздела требуется создать таблицу MyTestDefaultCol2 в образце базы данных AdventureWorks в схеме dbo. Чтобы создать эту таблицу, в редакторе запросов среды Microsoft SQL Server Management Studio выполните следующий запрос.

USE AdventureWorks;
GO
CREATE TABLE MyTestDefaultCol2 
(Col1 smallint,
Col2 nvarchar(50) DEFAULT 'Default value of Col2',
Col3 nvarchar(50) 
);
GO

Заметьте, что во втором столбце таблицы (Col2) хранится значение по умолчанию.

Образец файла форматирования

Некоторые примеры массового импорта используют не XML-файл форматирования MyTestDefaultCol2-f-c.Fmt, в точности соответствующий таблице MyTestDefaultCol2. Для создания этого файла форматирования в командной строке Microsoft Windows введите следующую команду.

bcp AdventureWorks..MyTestDefaultCol2 format nul -c -f C:\MyTestDefaultCol2-f-c.Fmt -t, -r\n -T

Дополнительные сведения о файлах форматирования см. в разделе Создание файла форматирования.

Образец файла данных

Следующий пример использует образец файла данных MyTestEmptyField2-c.Dat, второе поле которого не содержит значений. Файл данных MyTestEmptyField2-c.Dat содержит следующие записи.

1,,DataField3
2,,DataField3

Сохранение значение NULL при работе с командой bcp или инструкцией BULK INSERT

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

Команда	Квалификатор	Тип квалификатора
bcp	-k	Параметр
BULK INSERT	KEEPNULLS1	Аргумент

1 Для инструкции BULK INSERT: если значение по умолчанию недоступно, то для столбца таблицы должны быть допустимы значения NULL.

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

Дополнительные сведения см. в разделах Программа bcp и BULK INSERT (Transact-SQL).

Примеры

Примеры в этом разделе выполняют массовый импорт данных при помощи команды bcp или инструкции BULK INSERT и сохраняют значения NULL.

Для второго столбца таблицы (Col2) задано значение по умолчанию. Соответствующее поле файла данных содержит пустые строки. По умолчанию при импортировании данных из этого файла в таблицу MyTestDefaultCol2 командой bcp или инструкцией BULK INSERT в столбец Col2 вставляется значение по умолчанию.

1	Default value of Col2	DataField3
2	Default value of Col2	DataField3

Чтобы вставить значение NULL вместо Default value of Col2, необходимо в команде bcp или инструкции BULK INSERT указать ключ -k или параметр KEEPNULL (см. следующие примеры).

Сохранение значений NULL при работе с командой bcp

Следующий пример демонстрирует, как сохранить значения NULL при использовании команды bcp. Команда bcp поддерживает следующие параметры:

Параметр	Описание
-f	Позволяет указать файл форматирования.
-k	Указывает, что для пустых столбцов при выполнении данной операции необходимо вместо значений по умолчанию вставлять значения NULL.
-T	Указывает, что программа bcp подключается к серверу SQL Server, используя доверенное соединение.

Введите в командной строке Windows:

bcp AdventureWorks..MyTestDefaultCol2 in C:\MyTestEmptyField2-c.Dat -f C:\MyTestDefaultCol2-f-c.Fmt -k -T

Сохранение значений NULL при работе с инструкцией BULK INSERT

Следующий пример демонстрирует применение параметра KEEPNULLS в операторе BULK INSERT. В редакторе запросов, например в среде SQL Server Management Studio, выполните следующий запрос.

USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
   FROM 'C:\MyTestEmptyField2-c.Dat'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      KEEPNULLS
   );
GO

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

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

Команда	Квалификатор	Тип квалификатора
INSERT ... SELECT * FROM OPENROWSET(BULK...)	WITH(KEEPDEFAULTS)	Табличная подсказка

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

Примеры

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

Чтобы выполнить эти примеры, необходимо создать образец таблицы MyTestDefaultCol2, файл данных MyTestEmptyField2-c.Dat и использовать файл форматирования MyTestDefaultCol2-f-c.Fmt. Дополнительные сведения о создании этих образцов см. в подразделе «Образец таблицы и файла данных» ранее в этом разделе.

Для второго столбца таблицы (Col2) задано значение по умолчанию. Соответствующее поле файла данных содержит пустые строки. По умолчанию при импорте данных в таблицу MyTestDefaultCol2 инструкцией INSERT ... SELECT * FROM OPENROWSET(BULK...) в столбец Col2 вместо значений по умолчанию будут вставлены значения NULL. В этом случае результат будет выглядеть следующим образом.

1	NULL	DataField3
2	NULL	DataField3

Чтобы вставить значение по умолчанию Default value of Col2 вместо значения NULL, необходимо использовать табличную подсказку KEEPDEFAULTS (см. следующий пример). В редакторе запросов, например в среде SQL Server Management Studio, выполните:

USE AdventureWorks;
GO
INSERT INTO MyTestDefaultCol2
    WITH (KEEPDEFAULTS)
    SELECT *
      FROM OPENROWSET(BULK  'C:\MyTestEmptyField2-c.Dat',
      FORMATFILE='C:\MyTestDefaultCol2-f-c.Fmt'     
      ) as t1 ;
GO

См. также

Основные понятия

Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных
Изменение и создание определений DEFAULT

Другие ресурсы

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

Справка и поддержка

Получение помощи по SQL Server 2005