Форматирование файлов для импорта и экспорта данных (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, или-w-c) -Nили BULK INSERT операция указывает DATAFILETYPE параметр, указанный формат данных используется в качестве метода интерпретации полей файла данных.

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