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

Применимо к: даSQL Server (все поддерживаемые версии) ДаБаза данных SQL Azure ДаУправляемый экземпляр SQL Azure даAzure Synapse Analytics даПараллельное хранилище данных

Файл данных может содержать поля, выстроенные в порядке, отличном от порядка соответствующих столбцов в таблице. В этом подразделе описывается изменение файлов форматирования в формате XML и в формате, отличном от XML для импорта файла данных, поля которого выстроены в ином порядке, нежели столбцы таблицы. Измененный файл форматирования сопоставляет поля данных соответствующим столбцам таблицы. Ознакомьтесь с разделом Создание файла форматирования (SQL Server) для получения дополнительных сведений.

Контур
Пример условий теста
 ● Образец таблицы
 ● Образец файла данных
Создание файлов форматирования
 ● Создание файла форматирования в формате, отличном от XML
 ● Изменение файла форматирования в формате, отличном от XML
 ● Создание XML-файла форматирования
 ● Изменение XML-файла форматирования
Импорт данных с использованием файла форматирования для сопоставления столбцов таблицы полям файла данных
 ● Использование bcp и файла форматирования в формате, отличном от XML
 ● Использование bcp и XML-файла форматирования
 ● Использование BULK INSERT и файла форматирования в формате, отличном от XML
 ● Использование BULK INSERT и XML-файла форматирования
 ● Использование OPENROWSET(BULK…) и файла форматирования в формате, отличном от XML
 ● Использование OPENROWSET(BULK…) и XML-файла форматирования

Примечание

И файлы форматирования, отличные от XML, и XML-файлы форматирования можно использовать для массового импорта файла данных в таблицу с помощью команды служебной программы bcp, а также инструкции BULK INSERT или INSERT... SELECT * FROM OPENROWSET(BULK...). Дополнительные сведения см. в разделе Использование файла форматирования для массового импорта данных (SQL Server).

Пример условий теста

Примеры измененных файлов форматирования базируются на таблице и файле данных, которые определены ниже.

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

Приведенный ниже сценарий создает тестовую базу данных и таблицу с именем myRemap. Выполните следующий код Transact-SQL в Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myRemap
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   Gender char(1)
   );

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

В представленных ниже данных FirstName и LastName приведены в обратном порядке, как показано в таблице myRemap. С помощью Блокнота создайте пустой файл D:\BCP\myRemap.bcp и вставьте следующие данные:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Создание файлов форматирования

Для массового импорта из файла myRemap.bcp в таблицу myRemap файл форматирования должен сделать следующий код:

  • сопоставить первое поле данных с первым столбцом, PersonID;
  • сопоставить второе поле данных с третьим столбцом, LastName;
  • сопоставить третье поле данных со вторым столбцом, FirstName;
  • сопоставить четвертое поле данных с четвертым столбцом, Gender.

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

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

Дополнительные сведения см. в разделе Файлы формата, отличные от XML (SQL Server) . Следующая команда будет использовать служебную программу bcp для создания файла форматирования myRemap.fmtв формате, отличном от XML, на основе схемы myRemap. Кроме того, квалификатор c используется для указания символьных данных, t, используется для указания запятую в качестве признака конца поля, а T используется для указания доверенного соединения с использованием встроенной системы безопасности. В командной строке введите следующую команду:

bcp TestDatabase.dbo.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T

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

Терминологию см. в разделе Структура файлов форматирования в формате, отличном от XML . Откройте D:\BCP\myRemap.fmt в Блокноте и внесите следующие изменения:

  1. Переупорядочите строки файла форматирования, чтобы они отображались в том же порядке, что и данные в myRemap.bcp.
  2. Убедитесь, что значения порядка полей в файле узлов идут последовательно.
  3. Убедитесь, что после последней строки файла форматирования расположен символ возврата каретки.

Сравните изменения:
До:

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID               ""
2       SQLCHAR    0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

После:

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID               ""
2       SQLCHAR    0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

Теперь измененный файл форматирования отображает:

  • Первое поле данных в myRemap.bcp сопоставляется с первым столбцом. myRemap.. PersonID
  • Второе поле данных в myRemap.bcp сопоставляется с третьим столбцом. myRemap.. LastName
  • Третье поле данных в myRemap.bcp сопоставляется со вторым столбцом. myRemap.. FirstName
  • Четвертое поле данных в myRemap.bcp сопоставляется с четвертым столбцом. myRemap.. Gender

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

Дополнительные сведения см. в разделе XML-файлы форматирования (SQL Server) . Следующая команда будет использовать служебную программу bcp для создания XML-файла форматирования myRemap.xmlна основе схемы myRemap. Кроме того, квалификатор c используется для указания символьных данных, t, используется для указания запятую в качестве признака конца поля, а T используется для указания доверенного соединения с использованием встроенной системы безопасности. Для создания XML-файла форматирования должен использоваться квалификатор x . В командной строке введите следующую команду:

bcp TestDatabase.dbo.myRemap format nul -c -x -f D:\BCP\myRemap.xml -t, -T

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

Терминологию см. в разделе Синтаксис схемы для XML-файлов форматирования . Откройте D:\BCP\myRemap.xml в Блокноте и внесите следующие изменения:

  1. Порядок, в котором объявлены элементы <FIELD> в файле форматирования, является порядком, в котором эти поля будут расположены в файле данных, поэтому обратите порядок элементов <FIELD> с атрибутами ID, равными 2 и 3.
  2. Убедитесь, что значения атрибутов ID <FIELD> идут последовательно.
  3. Порядок элементов <COLUMN> в элементе <ROW> задает порядок, в котором они будут возвращены в целевой объект массовой операцией. XML-файл форматирования назначает каждому элементу <COLUMN> локальное имя, не имеющее отношения к столбцу целевой таблицы операции массового импорта. Порядок элементов <COLUMN> не зависит от порядка элементов <FIELD> в определении <RECORD>. Каждый элемент <COLUMN> соответствует элементу <FIELD> (чей идентификатор задан в атрибуте SOURCE элемента <COLUMN>). Таким образом, единственными атрибутами, требующими пересмотра, являются значения <COLUMN> SOURCE. Обратите порядок атрибутов 2 и 3 <COLUMN> SOURCE.

Сравните изменения:
До:

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
 </ROW>
</BCPFORMAT>

После:

<?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="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
 </ROW>
</BCPFORMAT>

Теперь измененный файл форматирования отображает:

  • Поле 1, соответствующее столбцу 1, пересопоставляется с первым столбцом таблицы. myRemap.. PersonID
  • Поле 2, соответствующее столбцу 2, пересопоставляется с третьим столбцом таблицы. myRemap.. LastName
  • Поле 3, соответствующее столбцу 3, пересопоставляется со вторым столбцом таблицы. myRemap.. FirstName
  • Поле 4, соответствующее столбцу 4, пересопоставляется с четвертым столбцом таблицы. myRemap.. Gender

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

В приведенных ниже примерах используется база данных, файл данных и файлы форматирования, созданные ранее.

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

В командной строке введите следующую команду:

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T

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

В командной строке введите следующую команду:

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.xml -T

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

Выполните следующий код Transact-SQL в Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myRemap;
BULK INSERT dbo.myRemap   
   FROM 'D:\BCP\myRemap.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

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

Выполните следующий код Transact-SQL в Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myRemap;
BULK INSERT dbo.myRemap   
   FROM 'D:\BCP\myRemap.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myRemap.xml');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

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

Выполните следующий код Transact-SQL в Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;
INSERT INTO dbo.myRemap
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myRemap.bcp',
        FORMATFILE = 'D:\BCP\myRemap.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

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

Выполните следующий код Transact-SQL в Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myRemap;
INSERT INTO dbo.myRemap 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myRemap.bcp',
        FORMATFILE = 'D:\BCP\myRemap.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

См. также:

bcp Utility
Пропуск столбца таблицы с помощью файла форматирования (SQL Server)
Использование файла форматирования для пропуска поля данных (SQL Server)