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

Применимо к:SQL ServerБаза данных Azure SQLУправляемый экземпляр Azure SQLAzure Synapse AnalyticsАналитическая платформа (PDW)

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

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

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

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

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

Следующий скрипт 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.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. Измените порядок атрибутов 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
• Использование файла форматирования для пропуска столбца таблицы (SQL Server)
• Использование файла форматирования для пропуска поля данных (SQL Server)