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

  • Статья

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

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

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

USE AdventureWorks2012;
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 AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

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

Примечание по безопасностиПримечание по безопасности

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

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

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

ПримечаниеПримечание

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

Методы изменения файла форматирования в формате, отличном от 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       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. Чтобы использовать BULK INSERT для массового импорта файла данных myTestSkipCol2.dat, в редакторе запросов среды Среда SQL Server Management Studio выполните следующий код:

USE AdventureWorks2012;
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 AdventureWorks2012..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-файлы форматирования (SQL Server).

Примеры

В примерах этого раздела используется образец таблицы 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 AdventureWorks2012;
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 AdventureWorks2012;
GO
BULK INSERT v_myTestSkipCol
FROM 'C:\myTestSkipCol2.dat'
WITH (FORMATFILE='C:\myTestSkipCol2.xml');
GO

См. также

Справочник

Программа bcp

BULK INSERT (Transact-SQL)

OPENROWSET (Transact-SQL)

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

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

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

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