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

Статья
04/04/2023

Применимо к: SQL Server Azure SQL Database Управляемый экземпляр SQL Azure Azure Synapse Analytics Analytics Platform System (PDW)

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

Примечание

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

Примечание

Этот синтаксис, включая массовую вставку, не поддерживается в Azure Synapse Analytics. В Azure Synapse Analytics и других платформах облачных баз данных перемещайте данные с помощью инструкции COPY в Фабрика данных Azure или с помощью инструкций T-SQL, таких как COPY INTO и PolyBase.

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

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

Пример таблицы

Приведенный ниже сценарий создает тестовую базу данных и таблицу с именем 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 в Блокноте и внесите следующие изменения:

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

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

До:

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 в Блокноте и внесите следующие изменения:

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

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

До:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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="http://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;

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

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

Пример таблицы

Пример файла данных

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

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

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

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

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

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

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

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

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

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

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

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

Дальнейшие действия

Обратная связь

Обратная связь

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