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

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics 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 構文を使用することをお勧めします。

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 フォーマット ファイル (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)」を参照してください。

XML 以外のフォーマット ファイル

この記事の Transact-SQL コード サンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。 Adventure Works Cycles は、データベースの概念とシナリオを説明するために使用する架空の製造会社です。

XML 以外のフォーマット ファイルを作成する

bcp コマンドを使用してフォーマット ファイルを作成するには、format 引数を指定し、データ ファイルのパスの代わりに nul を使用します。 format オプションには、-f のように、bcp <table_or_view> format nul -f <format_file_name> オプションも必要です。

XML 以外のフォーマット ファイルであることを区別するには、.fmt のように、ファイル名拡張子として MyTable.fmt を使用することをお勧めします。

XML 以外のフォーマット ファイルの構造およびフィールドの詳細については、「XML 以外のフォーマット ファイルを使用する (SQL Server)」を参照してください。

例

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

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

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

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

修飾子	説明
format nul -f <format_file>	XML 以外のフォーマット ファイルを指定します。
-n	ネイティブ データ型を指定します。
-T	bcp ユーティリティが統合セキュリティを使用した信頼関係接続を使用して SQL Server に接続することを指定します。 -T を指定しない場合、正常にログインするには -U と -P を指定する必要があります。

Windows コマンド プロンプトで、次の bcp コマンドを入力します。

bcp AdventureWorks2022.HumanResources.Department format nul -T -n -f Department-n.fmt

生成されるフォーマット ファイル Department-n.fmtには、次の情報が含まれます。

12.0
4
1  SQLSMALLINT   0       2       ""   1     DepartmentID         ""
2  SQLNCHAR      2       100     ""   2     Name                 SQL_Latin1_General_CP1_CI_AS
3  SQLNCHAR      2       100     ""   3     GroupName            SQL_Latin1_General_CP1_CI_AS
4  SQLDATETIME   0       8       ""   4     ModifiedDate         ""

詳細については、「XML 以外のフォーマット ファイルを使用する (SQL Server)」を参照してください。

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

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

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

修飾子	説明
format nul -f <format_file>	XML 以外のフォーマット ファイルを指定します。
-c	文字データを指定します。
-T	bcp ユーティリティが統合セキュリティを使用した信頼関係接続を使用して SQL Server に接続することを指定します。 -T を指定しない場合、正常にログインするには -U と -P を指定する必要があります。

Windows コマンド プロンプトで、次の bcp コマンドを入力します。

bcp AdventureWorks2022.HumanResources.Department format nul -c -f Department-c.fmt -T

生成されるフォーマット ファイル Department-c.fmtには、次の情報が含まれます。

12.0
4
1  SQLCHAR       0       7       "\t"     1     DepartmentID            ""
2  SQLCHAR       0       100     "\t"     2     Name                    SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR       0       100     "\t"     3     GroupName               SQL_Latin1_General_CP1_CI_AS
4  SQLCHAR       0       24      "\r\n"   4     ModifiedDate            ""

詳細については、「XML 以外のフォーマット ファイルを使用する (SQL Server)」を参照してください。

C: Unicode ネイティブ データ用の XML 以外のフォーマット ファイルを作成する

HumanResources.Department テーブルの、Unicode ネイティブ データ用の XML 以外のフォーマット ファイルを作成するには、次のコマンドを使用します。

bcp AdventureWorks2022.HumanResources.Department format nul -T -N -f Department-n.fmt

Unicode ネイティブ データの使用方法の詳細については、「Unicode ネイティブ形式を使用したデータのインポートまたはエクスポート (SQL Server)」を参照してください。

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

既定のターミネータを使用する HumanResources.Department テーブルの、Unicode 文字データ用の XML 以外のフォーマット ファイルを作成するには、次のコマンドを使用します。

bcp AdventureWorks2022.HumanResources.Department format nul -T -w -f Department-w.fmt

Unicode 文字データの使用方法の詳細については、「Unicode 文字形式を使用してデータをインポートまたはエクスポートする (SQL Server)」を参照してください。

F. コード ページ オプションを使用してフォーマット ファイルを使用する

bcp を使用して (つまり bcp format を使用して) フォーマット ファイルを作成する場合、照合順序/コード ページに関する情報がフォーマット ファイルに記述されます。

次に、照合順序が含まれる、5 つの列があるテーブル用のフォーマット ファイルの例を示します。

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0          Cyrillic_General_CS_AS
2  SQLCHAR         0       0       "\t"             2     c_1          Cyrillic_General_CS_AS
3  SQLCHAR         0       3000    "\t"             3     c_2          Cyrillic_General_CS_AS
4  SQLCHAR         0       5       "\t"             4     c_3          ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""

"bcp in -c -C65001 -f format_file ..." または "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ..." を使用して SQL Server にデータをインポートすると、65001 を超える照合順序またはコード ページ情報のオプションが存在するようになります。

そのため、フォーマット ファイルを生成する場合は、SQL Server にデータをインポートし直す前に、生成されたフォーマット ファイルから照合順序の情報を削除する必要があります。

次に照合順序情報を含まないフォーマット ファイルの例を示します。

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0              ""
2  SQLCHAR         0       0       "\t"             2     c_1              ""
3  SQLCHAR         0       3000    "\t"             3     c_2              ""
4  SQLCHAR         0       5       "\t"             4     c_3              ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""

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

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

• フォーマット ファイルを使用してテーブル列をスキップする (SQL Server)
• フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)
• フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)

関連するコンテンツ

• bcp ユーティリティ
• XML 以外のフォーマット ファイルを使用する (SQL Server)
• XML フォーマット ファイル (SQL Server)