Поделиться через

Форматирование файлов для импорта и экспорта данных (SQL Server)

  • Статья

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

При массовом импорте данных в таблицу SQL Server или массовом экспорте данных из таблицы можно использовать файл форматирования для хранения всех сведений о формате, необходимых для массового экспорта или массового импорта данных. Это включает сведения о формате каждого поля в файле данных для этой таблицы.

SQL Server поддерживает два типа файлов форматирования: форматы XML и файлы форматирования, отличные от XML. Файлы как в формате XML, так и в других форматах содержат описания каждого поля в файле данных, а файлы в формате XML также содержат описания соответствующих столбцов таблицы. Как правило, XML-файлы и файлы форматирования в формате, отличном от XML взаимозаменяемы. Однако рекомендуется пользоваться XML-синтаксисом новых файлов форматирования, так как он обеспечивает ряд преимуществ перед файлами форматирования в формате, отличном от XML. Дополнительные сведения см. в разделе XML Format Files (SQL Server) (SQL Server).

Примечание.

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

Преимущества файлов форматирования

Файлы форматирования предоставляют гибкую систему для записи файлов данных, требующую минимального или полного отсутствия редактирования для соответствия другим форматам данных или для чтения файлов данных из другого программного обеспечения.

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

Примеры файлов форматирования

В следующих примерах показана структура файлов форматирования в формате, отличном от XML, и XML-файлов форматирования. Эти файлы форматирования соответствуют таблице HumanResources.myTeam в образце базы данных AdventureWorks2022 . Эта таблица содержит четыре столбца: EmployeeID, Name, Titleи ModifiedDate.

Примечание.

Сведения об этой таблице и ее создании см. в статье HumanResources.myTeam sample table (SQL Server).

А. Использование файла форматирования, отличного от XML

Следующий файл форматирования, отличный от XML, использует собственный формат данных SQL Server для HumanResources.myTeam таблицы. Этот файл форматирования был создан с помощью следующей команды bcp :

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T

Команда bcp по умолчанию использует локальный экземпляр SQL Server по умолчанию с проверкой подлинности Windows. Вы можете указать другие сведения об экземпляре и имени входа. Для получения дополнительной информации см. утилиту bcp. Например, чтобы указать именованный удаленный сервер с проверкой подлинности Windows, используйте:

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T -S servername/instancename

Содержимое этого файла форматирования выглядит следующим образом, начиная с основного номера версии SQL Server и сведений о метаданных таблицы.

14.0
4
1       SQLSMALLINT   0       2       ""   1     EmployeeID               ""
2       SQLNCHAR      2       100     ""   2     Name                     SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     Title                    SQL_Latin1_General_CP1_CI_AS
4       SQLNCHAR      2       100     ""   4     Background               SQL_Latin1_General_CP1_CI_AS

Дополнительные сведения см. в разделе "Использование файлов форматирования, отличных от XML(SQL Server)".

B. Использование XML-файла форматирования

Следующий XML-файл форматирования использует собственный формат данных SQL Server для HumanResources.myTeam таблицы. Этот файл форматирования был создан с помощью следующей команды bcp :

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.xml -x -n -T

Файл форматирования содержит:

<?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="NativePrefix" LENGTH="1"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

Дополнительные сведения см. в разделе XML Format Files (SQL Server) (SQL Server).

Когда требуется файл форматирования?

Файлы форматирования обычно требуются в следующих случаях:

  • При использовании инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...) .

  • Для сложных ситуаций массового импорта с помощью bcp или BULK INSERT.

  • один и тот же файл данных используется в качестве источника для нескольких таблиц с разными схемами;

  • Число полей в файле данных отличается от количества столбцов в целевой таблице; например:

    • Целевая таблица содержит по крайней мере один столбец, для которого определяется значение по умолчанию или NULL разрешено.
    • У пользователей нет SELECT/INSERT разрешений на один или несколько столбцов в таблице.
    • один и тот же файл данных используется для двух или более таблиц с разными схемами.

  • порядок столбцов в файле данных отличается от порядка столбцов в таблице;

  • завершающие символы или длины префиксов отличаются в столбцах файла данных.

Примечание.

При отсутствии файла формата, если команда bcp указывает на переключатель формата данных (-n, -c, -w или -N) или операция BULK INSERT указывает опцию DATAFILETYPE, указанный формат данных используется по умолчанию для интерпретации полей файла данных.

Связанный контент