Статья
04/01/2012

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

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

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

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

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

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

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

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

USE AdventureWorks2008R2;
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 AdventureWorks2008R2..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 AdventureWorks2008R2..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 AdventureWorks2008R2;
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) задано значение по умолчанию. Соответствующее поле файла данных содержит пустые строки. Если инструкция INSERT ... SELECT * FROM OPENROWSET(BULK...) используется для импорта полей из этого файла данных в таблицу MyTestDefaultCol2, по умолчанию в столбец Col2 вместо значений по умолчанию будут вставлены значения NULL. В этом случае результат будет выглядеть следующим образом.

1	NULL	DataField3
2	NULL	DataField3

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

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