Использование файла форматирования для сопоставления столбцов таблицы с полями файла данных (SQL Server)
Применимо: SQL Server
База данных SQL Azure Управляемый экземпляр 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;