Share via

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

  • Статья

При импорте данных в таблицу команда 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.

Get-Help Квалификатор Тип квалификатора
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", необходимо использовать -k параметр switch или KEEPNULL, как показано в следующих примерах bcp и BULK INSERT.

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

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

Get-Help Квалификатор Тип квалификатора
Инструкции 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) задано значение по умолчанию. Соответствующее поле файла данных содержит пустые строки. При вставке ... 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

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

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

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

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

См. также:

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