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

Статья
12/15/2008

Изменения: 17 июля 2006 г.

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

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

Для следующих примеров требуется таблица с именем 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.

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

default non-XML format file for myTestSkipCol

Примечание.
Дополнительные сведения о полях файла форматирования см. в разделе Основные сведения о файлах форматирования не в формате 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. Также должны быть установлены в значение 0 (NULL) поля «признак конца» и «сортировка столбца».
Для значения поля «порядковый номер столбца на сервере» необходима непустая строка, хотя действительное имя столбца не требуется. Для оставшихся полей форматирования требуются значения по умолчанию.
Следующий пример основан на файле форматирования по умолчанию для таблицы myTestSkipCol. Значения, которые должны быть равны 0 или NULL, выделены полужирным шрифтом.
```
9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       0      ""     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. 2Чтобы использовать 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

См. также

Журнал изменений

Версия	Журнал
17 июля 2006 г.	Добавления Добавлены команды bcp для создания образцов файла форматирования. Изменения Обновлены следующие разделы для работы с образцом таблицы myTestSkipCol. Создание не XML-файла форматирования по умолчанию» «Методы изменения не XML-файла форматирования» «Создание XML-файла файла форматирования по умолчанию»

17 июля 2006 г.

Добавления

Добавлены команды bcp для создания образцов файла форматирования.

Изменения

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

Share via

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

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

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

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

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

Примеры

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

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

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

Примеры

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

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

См. также

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

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

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

Журнал изменений

Дополнительные ресурсы