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

Статья
06/25/2013

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

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

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

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

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

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

Для выполнения примеров этого раздела требуется создать таблицу 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

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

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

Следующий пример использует образец файла данных 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) задано значение по умолчанию. Соответствующее поле файла данных содержит пустые строки. При использовании инструкции 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 AdventureWorks;
GO
INSERT INTO MyTestDefaultCol2
    WITH (KEEPDEFAULTS)
    SELECT *
      FROM OPENROWSET(BULK  'C:\MyTestEmptyField2-c.Dat',
      FORMATFILE='C:\MyTestDefaultCol2-f-c.Fmt'     
      ) as t1 ;
GO