データをインポートまたはエクスポートするためのフォーマット ファイル (SQL Server)

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

SQL Server テーブルにデータを一括インポートしたり、テーブルからデータを一括エクスポートしたりする場合、"フォーマット ファイル" を使って、データの一括エクスポートと一括インポートに必要なすべてのフォーマット情報を格納できます。 これには、そのテーブルに対応するデータ ファイル内の各フィールドのフォーマット情報が含まれます。

SQL Server では、XML と XML 以外の 2 種類のフォーマット ファイルがサポートされています。 XML 以外のフォーマット ファイルにも XML フォーマット ファイルにもデータ ファイル内のすべてのフィールドの説明が含まれており、XML フォーマット ファイルには対応するテーブル列の説明も含まれています。 通常は、XML フォーマット ファイルと XML 以外のフォーマット ファイルの間には互換性があります。 ただし、XML フォーマット ファイルの方が XML 以外のフォーマット ファイルよりも優れた点がいくつかあるので、新しいフォーマット ファイルには XML 構文を使用することをお勧めします。 詳細については、「XML フォーマット ファイル (SQL Server)」を参照してください。

Note

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

フォーマット ファイルの利点

フォーマット ファイルは、他のデータ形式に準拠したり、他のソフトウェアからデータ ファイルを読み取るための編集をほとんど (あるいはまったく) 必要としない、データ ファイルを出力できる柔軟なシステムが提供します。

データ ファイルにおいて、不要なデータを追加または削除したり、既存のデータを並べ替えたりしなくても、データを一括インポートできます。 フォーマット ファイルは、データ ファイルのフィールドとテーブルの列間に不一致がある場合に役立つことがあります。

フォーマット ファイルの例

次の例では、XML 以外のフォーマット ファイルと XML フォーマット ファイルのレイアウトを示します。 これらのフォーマット ファイルは、 HumanResources.myTeam サンプル データベースの AdventureWorks2022 テーブルに対応しています。 このテーブルには、 EmployeeID、 Name、 Title、および ModifiedDateという 4 つの列があります。

Note

このテーブルの詳細と当該テーブルの作成方法については、「HumanResources.myTeam サンプル テーブル (SQL Server)」を参照してください。

A. XML 以外のフォーマット ファイルを使用する

次に示す XML 以外のフォーマット ファイルでは、HumanResources.myTeam テーブルに SQL Server のネイティブ データ形式を使用します。 このフォーマット ファイルは、次の bcp コマンドを使用して作成されました。

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T

bcp コマンドの既定値は、Windows 認証を使用した SQL Server のローカルの既定のインスタンスです。 必要な場合、他のインスタンスとログイン情報を指定できます。詳細については、「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 フォーマット ファイルでは、HumanResources.myTeam テーブルに SQL Server のネイティブ データ形式を使用します。 このフォーマット ファイルは、次の 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 フォーマット ファイル (SQL Server)」を参照してください。

フォーマット ファイルが必要になるケース

次の状況では、通常、フォーマット ファイルを必要とします。

  • ステートメント INSERT ... SELECT * FROM OPENROWSET(BULK...) を使用する場合。

  • bcp または BULK INSERTを使用した複雑な一括インポートの場合。

  • 1 つのデータ ファイルが、スキーマが異なる複数のテーブルのソースとして使用される場合。

  • データ ファイルのフィールド数と対象のテーブルの列数が異なる場合。次に例を示します。

    • 対象テーブルには、既定値が定義されているか、 NULL 値が許可されている列が 1 つ以上含まれています。
    • ユーザーが本テーブルの 1 つ以上の列に対する SELECT/INSERT 権限を持っていません。
    • スキーマが異なる複数のテーブルで、1 つのデータ ファイルが使用されている。

  • 列の順序がデータ ファイルとテーブルとの間で異なる場合。

  • 終了文字またはプレフィックス長がデータ ファイルの列によって異なる場合。

Note

フォーマット ファイルが存在しない場合に、 bcp コマンドで data-format スイッチ (-n、-c、-w、または-N) を指定するか、BULK INSERT 操作で DATAFILETYPE オプションを指定すると、指定したデータ形式がデータ ファイルのフィールドを解釈するための既定の方法として使用されます。

関連するコンテンツ