次の方法で共有


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

このトピックでは、フォーマット ファイルについて説明します。 フォーマット ファイルを使用すると、データ ファイルにフィールドが存在しない場合にテーブル列のインポートをスキップできます。 スキップされる列に 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       00""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