bcp を使用してフォーマット ファイルを作成する (SQL Server)

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

この記事では、bcp ユーティリティ を使用して、特定のテーブルのフォーマット ファイルを作成する方法について説明します。 フォーマット ファイルは、指定されたデータ型のオプション (-n、-c、-w、または -N)、およびテーブルやビューの区切り記号から構成されます。

SQL Server テーブルに一括インポートする場合や、テーブルからデータを一括エクスポートする場合は、データ ファイルを書き込むための柔軟なシステムとしてフォーマット ファイルを使用できます。 フォーマット ファイルでは、他のデータ形式に準拠したり、他のソフトウェア プログラムからデータ ファイルを読み取るための編集を行う必要はほとんど (あるいはまったく) ありません。

制限事項

フォーマット ファイルの読み取りに使用される bcp ユーティリティ (bcp.exe) のバージョンは、フォーマット ファイルの作成に使用されたバージョン、またはそれ以降のバージョンである必要があります。 たとえば、SQL Server 2016 (13.x) の bcp を使用すると、SQL Server 2014 (12.x) の bcp によって生成されるバージョン 12.0 のフォーマット ファイルを読み取ることができますが、SQL Server 2014 (12.x) の bcp を使用すると、SQL Server 2016 (13.x) の bcp によって生成されるバージョン 13.0 のフォーマット ファイルを読み取ることができません。

Note

一括挿入を含むこの構文は、Azure Synapse Analytics ではサポートされていません。 Azure Synapse Analytics やその他のクラウド データベース プラットフォーム統合では、Azure Data Factory の COPY ステートメントを介して、または COPY INTO や PolyBase などの T-SQL ステートメントを使用してデータ移動を実行します。

フォーマット ファイルを作成する

SQL Server は、非 XML 形式と XML 形式の 2 種類のフォーマット ファイルをサポートしています。 XML 以外のフォーマットとは、以前のバージョンの SQL Server でサポートされる従来のフォーマットです。

通常は、XML フォーマット ファイルと XML 以外のフォーマット ファイルの間には互換性があります。 ただし、XML フォーマット ファイルの方が XML 以外のフォーマット ファイルよりも優れた点がいくつかあるので、フォーマット ファイルには XML 構文を使用することをお勧めします。

この記事の Transact-SQL コード サンプルは AdventureWorks2022 サンプル データベースを使用します。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 フォーマット ファイル (SQL Server)」を参照してください。

例

このセクションでは、bcp コマンドを使用して XML フォーマット ファイルを作成する方法を示す次の例について説明します。 HumanResources.Department テーブルには、 DepartmentID、 Name、 GroupName、および ModifiedDateの 4 つの列があります。

A. 文字データ用の XML フォーマット ファイルを作成する

次の例では、 Department.xmlテーブルに対して HumanResources.Department という名前の XML フォーマット ファイルを作成します。 このフォーマット ファイルでは、文字データ形式と、既定と異なるフィールド ターミネータ (,) が使用されます。 生成されたフォーマット ファイルの内容をコマンドの後に示します。

bcp コマンドには、次の修飾子が含まれます。

修飾子 説明
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 フォーマット ファイル (SQL Server)」を参照してください。 文字データの詳細については、「文字形式を使用してデータをインポートまたはエクスポートする (SQL Server)」を参照してください。

B. ネイティブ データ用の XML フォーマット ファイルを作成する

次の例では、 Department-n.xmlテーブルに対して HumanResources.Department という名前の XML フォーマット ファイルを作成します。 このフォーマット ファイルでは、ネイティブ データ型が使用されます。 生成されたフォーマット ファイルの内容をコマンドの後に示します。

bcp コマンドには、次の修飾子が含まれます。

修飾子 説明
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 フォーマット ファイル (SQL Server)」を参照してください。 ネイティブ データの使用方法の詳細については、「ネイティブ形式を使用したデータのインポートまたはエクスポート (SQL Server)」を参照してください。

データ フィールドをテーブル列にマップする

bcpによって作成されたフォーマット ファイルには、すべてのテーブル列が順番に記述されます。 テーブル行を再配置または削除する場合は、フォーマット ファイルを変更できます。 フィールドとテーブル列とが直接マップされないデータ ファイル用に、フォーマット ファイルをカスタマイズできます。 詳細については、次の記事をご覧ください。

関連するコンテンツ