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

Статья
06/13/2017

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

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

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

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

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

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

Примечание

Сведения об этой таблице и способах ее создания см. в разделе HumanResources.myTeam Sample Table (SQL Server).

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

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

bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Fmt -n -T   
The contents of this format file are as follows: 9.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).

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

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

bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Xml -x -n -T

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

 <?xml version="1.0"?>  
<BCPFORMAT xmlns="https://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).

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

Инструкция INSERT... SELECT * FROM OPENROWSET(BULK...) всегда требует наличия файла форматирования.

Для bcp или BULK INSERT использование файла форматирования в простых ситуациях необязательно, и в этом редко возникает необходимость. Однако при выполнении сложных операций массового импорта файл форматирования очень часто необходим.

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

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

Примечание

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

Связанные задачи

См. также:

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

Share via