Пропуск столбца таблицы с помощью файла форматирования

В этом подразделе описываются файлы форматирования. Если поле не существует в файле данных, то импорт столбца таблицы можно пропустить с помощью файла форматирования. Файл данных может содержать меньше полей, чем таблица столбцов, только если пропущенные столбцы необязательно определяемы и (или) имеют значение по умолчанию.

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

Для следующих примеров требуется таблица с именем myTestSkipCol в образце базы данных AdventureWorks схемы dbo. Создайте таблицу следующим образом:

USE AdventureWorks;
GO
CREATE TABLE myTestSkipCol 
   (
   Col1 smallint,
   Col2 nvarchar(50) NULL,
   Col3 nvarchar(50) not NULL
   );
GO

В примерах, приведенных ниже, используется образец файла данных myTestSkipCol2.dat, который содержит только два поля, в то время как в соответствующей таблице три столбца:

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

Чтобы выполнить массовый импорт данных из таблицы myTestSkipCol2.dat в таблицу myTestSkipCol, файл форматирования должен ставить в соответствие первое поле данных Col1, а второе поле данных Col3, пропуская поле Col2.

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

Чтобы пропустить столбец таблицы, можно изменить файл форматирования в формате, отличном от XML. Обычно это включает использование команды bcp для создания файлов форматирования в формате, отличном от XML по умолчанию и изменение файла по умолчанию в текстовом редакторе. Измененный файл форматирования должен сопоставлять все существующие поля соответствующим столбцам таблицы и указывать, какие столбцы или столбец таблицы пропускать. Есть два варианта изменения файлов форматирования в формате, отличном от XML по умолчанию. В одном случае поле данных не существует в файле данных, и в соответствующий столбец таблицы не будут записаны никакие данные.

Создание файла форматирования в формате, отличном от XML, по умолчанию

В этом разделе используется не XML-файл форматирования по умолчанию, созданный для образца таблицы myTestSkipCol с помощью следующей команды bcp:

bcp AdventureWorks..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

Предыдущая команда создает файл форматирования в формате, отличном от XML, myTestSkipCol_Default.fmt. Этот файл форматирования называется файлом форматирования по умолчанию, потому что это форма, созданная командой bcp. Обычно файл форматирования по умолчанию описывает сопоставления один к одному между полями файла данных и столбцами таблицы.

Примечание по безопасности
Необходимо указать имя экземпляра сервера, с которым осуществляется соединение. Возможно также потребуется указать имя пользователя и пароль. Дополнительные сведения см. в разделе Программа bcp.

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

Примечание
Дополнительные сведения о полях файла форматирования см. в разделе Основные сведения о файлах форматирования в формате, отличном от XML.

Методы изменения файла форматирования в формате, отличном от XML

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

• Рекомендуемый метод состоит из трех основных шагов. Сначала удалите все строки файла форматирования, описывающие пропущенные в файле данных поля. Затем уменьшите значение «порядкового номера поля в файле данных» каждой строки файла форматирования, которая следует за удаленной строкой. Целью являются последовательные значения «порядкового номера поля в файле данных», от 1 до n, которые отражают действительную позицию каждого поля данных в файле данных. Наконец, уменьшите значение в поле «Число столбцов» до действительного числа полей в файле данных.

  Следующий пример основан на файле форматирования по умолчанию для таблицы myTestSkipCol, создаваемой в подразделе «Создание файла форматирования в формате, отличном от XML» ранее в этом разделе. Этот измененный файл форматирования сопоставляет первое поле данных полю Col1, пропускает поле Col2 и сопоставляет второе поле данных Col3. Строка для поля Col2 была удалена. Другие изменения выделены полужирным шрифтом.

  9.0
  2
  1       SQLCHAR       0       7       "\t"     1     Col1         ""
  2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
  

• В качестве альтернативы, чтобы пропустить столбец таблицы, можно изменить определение строки файла форматирования, которая соответствует этому столбцу таблицы. В этой строке файла форматирования значения «длина префикса», «длина данных файла узла» и «порядковый номер столбца на сервере» должны быть равны 0. Также должны быть установлены в значение «» (NULL) поля «признак конца» и «параметры сортировки столбца».

  Для значения поля «порядковый номер столбца на сервере» необходима непустая строка, хотя действительное имя столбца не требуется. Для оставшихся полей форматирования требуются значения по умолчанию.

  Следующий пример основан на файле форматирования по умолчанию для таблицы myTestSkipCol. Значения, которые должны быть равны 0 или NULL, выделены полужирным шрифтом.

  9.0
  3
  1       SQLCHAR       0       7       "\t"     1     Col1         ""
  2       SQLCHAR       00""0     Col2         ""
  3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
  

Примеры

Следующие примеры также базируются на образце таблицы myTestSkipCol и образце файла данных myTestSkipCol2.dat, созданных в подразделе «Образец таблицы и файла данных» ранее в этом разделе.

Применение инструкции BULK INSERT

Следующий пример работает с использованием любого из файлов форматирования, отличных от XML и создаваемых в подразделе «Методы изменения не XML-файла форматирования» ранее в этом разделе. В этом примере измененный файл форматирования называется C:\myTestSkipCol2.fmt. Чтобы использовать BULK INSERT для массового импорта файла данных myTestSkipCol2.dat, в редакторе запросов среды SQL Server Management Studio выполните следующий код:

USE AdventureWorks;
GO
BULK INSERT myTestSkipCol 
   FROM 'C:\myTestSkipCol2.dat' 
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');
GO
SELECT * FROM myTestSkipCol;
GO

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

Если для импорта непосредственно в таблицу используется XML-файл форматирования, то с помощью команды bcp или инструкции BULK INSERT столбец пропустить нельзя. Однако можно выполнить импорт всех столбцов таблицы, кроме последнего. Если нужно пропустить все столбцы, кроме последнего, необходимо создать представление целевой таблицы, содержащее только столбцы из файла данных. После этого можно выполнить массовый импорт данных из этого файла в представление.

Чтобы пропустить столбец таблицы с помощью XML-файла форматирования с помощью инструкции OPENROWSET(BULK...), необходимо следующим образом подставить явный список столбцов в список выбора и в целевую таблицу:

INSERT ...<список_столбцов> SELECT <список_столбцов> FROM OPENROWSET(BULK...)

Создание XML-файла форматирования по умолчанию

Следующие примеры измененных файлов форматирования базируются на образце таблицы myTestSkipCol и файла данных, созданных в подразделе «Образец таблицы и файла данных» ранее в этом разделе. Следующая команда bcp создает XML-файл форматирования по умолчанию для таблицы myTestSkipCol.

bcp AdventureWorks..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

Результирующий не XML-файл форматирования описывает сопоставления один к одному между полями файла данных и столбцами таблицы следующим образом:

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Примечание
Дополнительные сведения о структуре не XML-файлов форматирования см. в разделе Основные сведения о XML-файлах форматирования.

Примеры

В примерах этого раздела используется образец таблицы myTestSkipCol и образец файла данных myTestSkipCol2.dat, создаваемых в подразделе «Образец таблицы и файла данных» ранее в этом разделе. Для импортирования данных из файла myTestSkipCol2.dat в таблицу myTestSkipCol в примерах используется следующий измененный XML-файл форматирования myTestSkipCol2-x.xml. Следующий пример основан на файле форматирования, создаваемом в подразделе «Создание не XML-файла форматирования по умолчанию» ранее в этом разделе.

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Применение инструкции OPENROWSET(BULK...)

В следующем примере используется поставщик массового набора строк OPENROWSET и файл форматирования myTestSkipCol2.xml. В примере выполняется массовый импорт файла данных myTestSkipCol2.dat в таблицу myTestSkipCol. Инструкция, в соответствии с требованиями, содержит явный список столбцов в списке выбора, а также в целевой таблице.

В редакторе запросов среды SQL Server Management Studio выполните:

USE AdventureWorks;
GO
INSERT INTO myTestSkipCol
  (Col1,Col3)
    SELECT Col1,Col3
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',
      FORMATFILE='C:\myTestSkipCol2.Xml'  
       ) as t1 ;
GO

Использование инструкции BULK IMPORT на представлениях

В следующем примере создается представление v_myTestSkipCol для таблицы myTestSkipCol. В этом представлении пропущен второй столбец таблицы Col2. Затем применяется инструкция BULK INSERT для импорта файла данных myTestSkipCol2.dat в это представление.

В редакторе запросов среды SQL Server Management Studio выполните:

CREATE VIEW v_myTestSkipCol AS
    SELECT Col1,Col3
    FROM myTestSkipCol;
GO

USE AdventureWorks;
GO
BULK INSERT v_myTestSkipCol
FROM 'C:\myTestSkipCol2.dat'
WITH (FORMATFILE='C:\myTestSkipCol2.xml');
GO

См. также

Справочник

Программа bcp

BULK INSERT (Transact-SQL)

OPENROWSET (Transact-SQL)

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

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

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

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