Создание файла форматирования с помощью bcp (SQL Server)

  • Статья

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

В этой статье описывается, как использовать служебную программу bcp для создания файла формата для определенной таблицы. Файл форматирования основан на указанном параметре типа данных (-n, -c-wили-N) и разделителях таблиц или представлений.

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

Ограничения

Версия служебной программы bcp (bcp.exe), используемая для чтения файла формата, должна совпадать с версией, используемой для создания файла форматирования. Например, sql Server 2016 (13.x) bcp может прочитать файл формата версии 12.0, созданный SQL Server 2014 (12.x), но bcp SQL Server 2014 (12.x) не может читать файл формата версии 13.0, который создается sql Server 2016 (13.x) bcp.

Примечание.

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

Создание файлов форматирования

SQL Server поддерживает два типа файлов форматирования: файлы форматирования в формате, отличном от XML, и XML-файлы форматирования. Формат, отличный от XML, — это исходный формат, поддерживаемый более ранними версиями SQL Server.

Как правило, XML-файлы и файлы форматирования в формате, отличном от XML взаимозаменяемы. Однако рекомендуется использовать синтаксис XML для файлов форматирования, так как они предоставляют несколько преимуществ для файлов форматирования, отличных от XML.

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server. Компания Adventure Works Cycles — это вымышленная производственная компания, которая используется для демонстрации концепций баз данных и сценариев работы с ними.

Создание XML-файла форматирования

Чтобы использовать команду bcp для создания файла форматирования, укажите format аргумент и используйте nul вместо пути к файлу данных. Для format этого параметра всегда требуется -f параметр, а также для создания XML-файла форматирования, необходимо также указать -x этот параметр, например bcp <table_or_view> format nul -f <format_file_name> -x.

Чтобы отличить XML-файл форматирования, рекомендуется использовать .xml в качестве расширения имени файла, например MyTable.xml.

Сведения о структуре и полях XML-файлов форматирования см. в разделе XML Format Files (SQL Server).

Примеры

В этом разделе содержатся следующие примеры, в котором показано, как использовать команды bcp для создания XML-файла форматирования. Таблица HumanResources.Department содержит четыре столбца: DepartmentID, Name, GroupNameи ModifiedDate.

А. Создание XML-файла форматирования для символьных данных

В следующем примере создается XML-файл форматирования Department.xmlдля таблицы HumanResources.Department . Файл форматирования использует формат символьных данных и признак конца поля, отличный от установленного по умолчанию (,). Содержимое созданного файла форматирования приведено после команды.

Команда bcp содержит следующие квалификаторы:

Квалификаторы Description
format nul -x -f <format_file> Задает XML-файл форматирования.
-c Задает символьные данные.
-t, Задает запятую (,) в качестве признака конца поля.

Примечание. Если файл данных использует терминатор полей по умолчанию (\t), -t параметр не требуется.
-T Указывает, что служебная программа bcp подключается к SQL Server с доверенным подключением с помощью интегрированной безопасности. Если -T он не указан, необходимо указать -U и -P успешно войти.

В командной строке Windows введите следующую команду bcp :

bcp AdventureWorks2022.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T

Созданный файл форматирования Department-c.xmlсодержит следующие XML-элементы:

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Сведения о синтаксисе этого файла форматирования см. в разделе XML Format Files (SQL Server). Сведения о символьных данных см. в разделе "Использование формата символов" для импорта или экспорта данных (SQL Server).

B. Создание XML-файла форматирования для собственных данных

В следующем примере создается XML-файл форматирования Department-n.xmlдля таблицы HumanResources.Department . в котором используются собственные типы данных. Содержимое созданного файла форматирования приведено после команды.

Команда bcp содержит следующие квалификаторы:

Квалификаторы Description
format nul -x -f <format_file> Задает XML-файл форматирования.
-n Указывает собственные типы данных.
-T Указывает, что служебная программа bcp подключается к SQL Server с доверенным подключением с помощью интегрированной безопасности. Если -T он не указан, необходимо указать -U и -P успешно войти.

В командной строке Windows введите следующую команду bcp :

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

Созданный файл форматирования Department-n.xmlсодержит следующие XML-элементы:

<?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="NativeFixed" LENGTH="2"/>
  <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="NativeFixed" LENGTH="8"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Сведения о синтаксисе этого файла форматирования см. в разделе XML Format Files (SQL Server). Сведения об использовании собственных данных см. в статье "Использование собственного формата для импорта или экспорта данных (SQL Server)".

Сопоставление полей данных с столбцами таблицы

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

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