Использование файла форматирования для сопоставления столбцов таблицы с полями файла данных (SQL Server)
Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse AnalyticsAnalytics 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;
Дальнейшие действия
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по