フォーマットファイルを使用したテーブル列のスキップ

[アーティクル]
12/16/2008

更新 : 2006 年 7 月 17 日

このトピックでは、フォーマットファイルについて説明します。フォーマットファイルを使用すると、データファイルにフィールドが存在しない場合にテーブル列のインポートをスキップできます。スキップされる列に NULL 値が許容されているか、既定値があるか、またはその両方の場合のみ、テーブルの列の数より少ないフィールドをデータファイルに含めることができます。

サンプルテーブルとデータファイル

次の例では、AdventureWorks サンプルデータベースで myTestSkipCol というテーブルを dbo スキーマを使用して作成する必要があります。このテーブルは次のように作成します。

USE AdventureWorks;
GO
CREATE TABLE myTestSkipCol 
   (
   Col1 smallint,
   Col2 nvarchar(50) NULL,
   Col3 nvarchar(50) not NULL
   );
GO

次の例では、myTestSkipCol2.dat というサンプルデータファイルを使用します。対応するテーブルには列が 3 つありますが、このファイルにはフィールドが 2 つしかありません。

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

myTestSkipCol2.dat から myTestSkipCol テーブルにデータを一括インポートするには、フォーマットファイルで最初のデータフィールドを Col1 にマップし、Col2 をスキップして 2 番目のフィールドを Col3 にマップする必要があります。

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

XML 以外のフォーマットファイルを変更して、テーブル列をスキップすることができます。通常、この作業は、bcp ユーティリティを使用して XML 以外の既定のフォーマットファイルを作成して、その既定のファイルをテキストエディタで変更する必要があります。変更後のフォーマットファイルでは、既存の各フィールドを対応するテーブル列にマップし、スキップする列を指定する必要があります。XML 以外の既定のデータファイルを変更するには、2 つの方法があります。どちらの方法も、データファイルにデータフィールドが存在しないこと、および対応するテーブル列にデータが挿入されないことを示します。

XML 以外の既定のフォーマットファイルの作成

ここでは、以下の bcp コマンドで myTestSkipCol サンプルテーブル用に作成した、XML 以外の既定のフォーマットファイルを使用します。

bcp AdventureWorks..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

上記のコマンドでは、myTestSkipCol_Default.fmt という XML 以外のフォーマットファイルを作成します。このフォーマットファイルは bcp で作成した形式なので、既定のフォーマットファイルといいます。既定のフォーマットファイルには通常、データファイルフィールドとテーブル列の一対一の対応が記述されます。

セキュリティメモ :
場合によっては、接続先サーバーインスタンスの名前を指定する必要があります。また、ユーザー名とパスワードの指定が必要な場合もあります。詳細については、「bcp ユーティリティ」を参照してください。

次の図に、この既定のフォーマットファイルのサンプルで使用されている値を示します。図には、各フォーマットファイルフィールドの名前も示しています。

myTestSkipCol 用の既定の非 XML 形式ファイル

メモ :
フォーマットファイルフィールドの詳細については、「XML 以外のフォーマットファイルについて」を参照してください。

XML 以外のフォーマットファイルを変更する方法

テーブル列をスキップするには、既定の XML 以外のフォーマットファイルを編集し、そのファイルを以下のいずれかの方法で変更します。

推奨されている方法には、3 つの基本的な手順が含まれています。まず、データファイルに存在しないフィールドを記述しているフォーマットファイルの行をすべて削除します。次に、削除したフォーマットファイルの行に続く各行の "ホストファイルフィールドの順序" の値を減らします。"ホストファイルフィールドの順序" の値が、データファイル内での各データフィールドの実際の場所を反映した 1 ～ n の通し番号になるようにします。最後に、データファイル内の実際のフィールド数が反映されるように、"列の数" フィールドの値を減らします。
次の例は、このトピックの「XML 以外の既定のフォーマットファイルの作成」で既に作成した myTestSkipCol テーブルの既定のフォーマットファイルを基にしています。変更後のフォーマットファイルは、最初のデータフィールドを Col1 にマップし、Col2 をスキップして、2 番目のデータフィールドを Col3 にマップしています。Col2 に相当する行は削除されています。他の変更点は太字で示してあります。
```
9.0
2
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
```
スキップするテーブル列に対応するフォーマットファイルの行の定義を変更することもできます。変更するフォーマットファイルの行は、"プレフィックス長"、"ホストファイルのデータ長"、および "サーバーの列の順序" の値を 0 に設定する必要があります。さらに、"ターミネータ" および "列の照合順序" のフィールド値を "" (NULL) に設定する必要があります。
"サーバーの列名" の値は、実際の列名が不要な場合でも、空でない文字列を指定する必要があります。残りのフォーマットフィールドは既定値のままにしておく必要があります。
次の例も、myTestSkipCol テーブルの既定のフォーマットファイルから作成しました。0 または NULL にする必要がある値を太字で示してあります。
```
9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       0      ""     0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
```

例

以下の例は、このトピックの「サンプルテーブルとデータファイル」で既に作成した myTestSkipCol サンプルテーブルおよび myTestSkipCol2.dat サンプルデータファイルを基にしています。

BULK INSERT の使用

次の例を動作させるには、このトピックの「XML 以外のフォーマットファイルを変更する方法」で既に作成した、変更後の XML 以外のフォーマットファイルの 1 つを使用します。この例では、変更後のフォーマットファイルの名前を C:\myTestSkipCol2.fmt とします。BULK INSERT を使用して myTestSkipCol2.dat データファイルを一括インポートするには、SQL Server Management Studio のクエリエディタで次のコードを実行します。

USE AdventureWorks;
GO
BULK INSERT myTestSkipCol 
   FROM 'C:\myTestSkipCol2.dat' 
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');
GO
SELECT * FROM myTestSkipCol;
GO

XML フォーマットファイルの使用

XML フォーマットファイルでは、bcp コマンドまたは BULK INSERT ステートメントを使用して直接テーブルにインポートする場合は、列をスキップできません。ただし、テーブルの最後の列を除くすべての列にインポートできます。最後の列を除く特定の列をスキップする必要がある場合、データファイルに含まれている列のみを含んでいる対象テーブルのビューを作成する必要があります。その後、データファイルからビューにデータを一括インポートできます。

OPENROWSET(BULK...) を使用してテーブル列をスキップするために XML フォーマットファイルを使用するには、次のように選択リストおよび対象テーブルの列リストを明示的に指定する必要があります。

INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...)

既定の XML フォーマットファイルの作成

変換後のフォーマットファイルの例は、このトピックの「サンプルテーブルとデータファイル」で既に作成した myTestSkipCol サンプルテーブルおよびデータファイルを基にしています。次の bcp コマンドを実行すると、myTestSkipCol テーブルの既定の XML フォーマットファイルが作成されます。

bcp AdventureWorks..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

作成される既定の XML フォーマットファイルには、次に示すように、データファイルフィールドとテーブル列の一対一の対応が記述されます。

<?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="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

メモ :
XML フォーマットファイルの構造については、「XML フォーマットファイルについて」を参照してください。

例

ここに記載している例は、このトピックの「サンプルテーブルとデータファイル」で既に作成した myTestSkipCol サンプルテーブルおよび myTestSkipCol2.dat サンプルデータファイルを使用します。myTestSkipCol2.dat から myTestSkipCol テーブルにデータをインポートするため、変更した XML フォーマットファイル myTestSkipCol2-x.xml を使用します。このファイルは、このトピックの「既定の XML フォーマットファイルの作成」で既に作成したフォーマットファイルを基にしています。

<?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="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

OPENROWSET(BULK...) の使用

次の例では、OPENROWSET 一括行セットプロバイダと myTestSkipCol2.xml フォーマットファイルを使用します。この例では、myTestSkipCol2.dat データファイルを myTestSkipCol テーブルに一括インポートします。必要に応じて、ステートメントでは、選択リストおよびターゲットテーブルの列の一覧を明示的に指定します。

SQL Server Management Studio のクエリエディタで、次のコードを実行します。

USE AdventureWorks;
GO
INSERT INTO myTestSkipCol
  (Col1,Col3)
    SELECT Col1,Col3
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',
      FORMATFILE='C:\myTestSkipCol2.Xml'  
       ) as t1 ;
GO

ビューに対する BULK IMPORT の使用

次の例では、myTestSkipCol テーブルに v_myTestSkipCol ビューを作成します。このビューでは 2 番目のテーブル列である Col2 がスキップされます。その後、BULK INSERT を使用して myTestSkipCol2.dat データファイルをこのビューにインポートします。

SQL Server Management Studio のクエリエディタで、次のコードを実行します。

CREATE VIEW v_myTestSkipCol AS
    SELECT Col1,Col3
    FROM myTestSkipCol;
GO

USE AdventureWorks;
GO
BULK INSERT v_myTestSkipCol
FROM 'C:\myTestSkipCol2.dat'
WITH (FORMATFILE='C:\myTestSkipCol2.xml');
GO

参照

変更履歴

リリース	履歴
2006 年 7 月 17 日	新しい内容 : サンプルフォーマットファイルを作成するための bcp コマンドを追加しました。変更内容 : myTestSkipCol サンプルテーブルを使用するため、次のセクションを更新しました。「XML 以外の既定のフォーマットファイルの作成」「XML 以外のフォーマットファイルを変更する方法」「既定の XML フォーマットファイルの作成」

2006 年 7 月 17 日

新しい内容 :

サンプルフォーマットファイルを作成するための bcp コマンドを追加しました。

変更内容 :

myTestSkipCol サンプルテーブルを使用するため、次のセクションを更新しました。
- 「XML 以外の既定のフォーマットファイルの作成」
- 「XML 以外のフォーマットファイルを変更する方法」
- 「既定の XML フォーマットファイルの作成」

フォーマット ファイルを使用したテーブル列のスキップ

サンプル テーブルとデータ ファイル

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

XML 以外の既定のフォーマット ファイルの作成

XML 以外のフォーマット ファイルを変更する方法

例

BULK INSERT の使用

XML フォーマット ファイルの使用

既定の XML フォーマット ファイルの作成

例