フォーマット ファイルの概要
フォーマット ファイルとは、特定のテーブルに関連するデータ ファイル内の各フィールドのフォーマット情報を保存するために使用するファイルです。フォーマット ファイルには、データの一括エクスポートや一括インポートに必要なすべてのフォーマット情報を指定します。フォーマット ファイルを使用すると、他のデータ形式に準拠したり、他のソフトウェアからデータ ファイルを読み取るための編集をほとんど (あるいはまったく) 行うことなく、データ ファイルを出力できる柔軟なシステムが実現します。
Microsoft SQL Server 2000 以前のバージョンでは、一括エクスポート操作と一括インポート操作に 1 種類のフォーマット ファイルを使用していました。このフォーマット ファイルは、現在でも使用できます。ただし SQL Server 2005 以降のバージョンでは、これとは別に XML フォーマット ファイルも使用できるようになりました。元の種類のフォーマット ファイルは、XML 以外のフォーマット ファイルと呼ばれます。
すべてのフォーマット ファイルには、データ ファイル内の各フィールドの説明が含まれます。また、XML フォーマット ファイルには、対応するテーブル列の説明も含まれます。通常は、XML フォーマット ファイルと XML 以外のフォーマット ファイルの間には互換性があります。ただし、XML フォーマット ファイルの方が XML 以外のフォーマット ファイルよりも優れた点がいくつかあるので、新しいフォーマット ファイルには XML 構文を使用することをお勧めします。XML フォーマット ファイルの特徴を次に示します。
自己記述形式であり、読み取り、作成、および拡張が容易です。
対象列のデータ型が格納されます。
これにより、データ ファイルでのデータの表記方法と、ファイル内の各フィールドに関連付けられるデータ型を切り離すことができます。たとえば、データ ファイルにデータの文字表記が含まれている場合、対応する SQL 列の型が失われます。
bcp コマンドと BULK INSERT ステートメントでは、対象のテーブル列を使用して型変換を行います。つまり、対象のテーブルが必要になります。これに対して OPENROWSET(BULK...) 関数では、XML フォーマット ファイルを使用してデータ ファイルからデータを読み取ります。つまり、対象のテーブルは不要です。
データ ファイルから、単一のラージ オブジェクト (LOB) データ型を格納するフィールドを読み込むことができます。
フォーマット ファイルの構文は、操作の方向とは関係がありません。つまり、一括エクスポートでも一括インポートでも構文は同じです。
各種フォーマット ファイルのレイアウトの詳細については、このトピックの「例」を参照してください。
フォーマット ファイルが必要になるケース
INSERT ...SELECT * FROM OPENROWSET(BULK...) ステートメントでは、常にフォーマット ファイルが必要です。
- bcp または BULK INSERT の場合、単純な状況では、フォーマット ファイルを使用しなくてもかまいません。必要になることはほとんどありません。ただし、複雑な一括インポート操作を実行する場合は、頻繁にフォーマット ファイルが必要になります。
次の場合は、フォーマット ファイルが必要です。
1 つのデータ ファイルが、スキーマが異なる複数のテーブルのソースとして使用される場合。
データ ファイルのフィールド数と対象のテーブルの列数が異なる場合。次に例を示します。
対象のテーブルに、既定値が定義されているか、NULL 値が許可されている列が 1 つ以上含まれている。
ユーザーがテーブルの 1 つ以上の列に対する SELECT/INSERT 権限を持っていない。
スキーマが異なる複数のテーブルで、1 つのデータ ファイルが使用されている。
列の順序がデータ ファイルとテーブルとの間で異なる場合。
終了文字またはプレフィックス長がデータ ファイルの列によって異なる場合。
.gif "注意") 注 |
|---|
フォーマット ファイルが存在しない場合に、bcp コマンドで data-format スイッチ (-n、-c、-w、または -N) を指定したり、BULK INSERT 操作で DATAFILETYPE オプションを指定すると、指定したデータ形式がデータ ファイルのフィールドを解釈するための既定の方法として使用されます。 |
例
次の例では、XML 以外のフォーマット ファイルと XML フォーマット ファイルのレイアウトを示します。これらのフォーマット ファイルは、AdventureWorks2008R2 サンプル データベースの HumanResources.myTeam テーブルに対応しています。このテーブルには、EmployeeID、Name、Title、および ModifiedDate という 4 つの列があります。
| 注 |
|---|
このテーブル、およびこのテーブルを作成する方法の詳細については、「HumanResources.myTeam テーブルの作成」を参照してください。 |
A. XML 以外のフォーマット ファイルの使用
次に示す XML 以外のフォーマット ファイルでは、HumanResources.myTeam テーブルに SQL Server ネイティブ データ形式を使用します。このフォーマット ファイルは、次の bcp コマンドを使用して作成されました。
bcp AdventureWorks2008R2.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
bcp AdventureWorks2008R2.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 以外のフォーマット ファイルについて」を参照してください。
B. XML フォーマット ファイルの使用
次に示す XML フォーマット ファイルでは、HumanResources.myTeam テーブルに SQL Server ネイティブ データ形式を使用します。このフォーマット ファイルは、次の bcp コマンドを使用して作成されました。
bcp AdventureWorks2008R2.HumanResources.myTeam format nul -f myTeam.Xml -x -n -T
bcp AdventureWorks2008R2.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 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 フォーマット ファイルについて」を参照してください。