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

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

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

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

Примечание.

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

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

Примеры измененных файлов формата в этой статье основаны на файле таблиц и данных, определенных далее в этой статье.

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

Следующий скрипт Transact-SQL создает тестовую базу данных и таблицу с именем myRemap.

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 импорт данных из myRemap.bcp таблицы, файл форматирования должен выполнить следующие действия:

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

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

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

Подробные сведения см . в статье об использовании файлов форматирования, отличных от XML (SQL Server ). Следующая команда использует служебную программу bcp для создания файла формата, отличного от XML, myRemap.fmtна основе схемы 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> 2 и 3.

2. Убедитесь, что значения атрибутов ID <FIELD> идут последовательно.

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

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

До:

<?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.

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.

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 и файла форматирования, отличного от XML

Выполните следующий скрипт Transact-SQL.

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 и XML-файла форматирования

Выполните следующий скрипт Transact-SQL.

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)