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

Применимо к:SQL ServerБаза данных Azure SQLУправляемый экземпляр Azure SQLAzure Synapse AnalyticsАналитическая платформа (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, файл форматирования должен выполнить следующие действия:

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

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

До

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