次の方法で共有

フォーマット ファイルを使用してテーブル列をスキップする (SQL Server)

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

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

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

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

次の例では、2 つのフィールドのみを含むサンプル データ ファイル myTestSkipCol2.datを使用しますが、対応するテーブルには 3 つの列が含まれています。

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

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

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

XML 以外のフォーマット ファイルを変更して、テーブル列をスキップできます。 通常、 bcp ユーティリティを 使用して既定の XML 以外のフォーマット ファイルを作成し、テキスト エディターで既定のファイルを変更する必要があります。 変更されたフォーマット ファイルは、既存の各フィールドを対応するテーブル列にマップし、スキップするテーブル列を示す必要があります。 XML 以外の既定のデータ ファイルを変更するための代替手段が 2 つあります。 いずれかの代替方法は、データ フィールドがデータ ファイルに存在せず、対応するテーブル列にデータが挿入されないことを示します。

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

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

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

前のコマンドは、XML 以外のフォーマット ファイルを作成 myTestSkipCol_Default.fmt。 このフォーマット ファイルは、bcp によって生成されるフォームであるため、既定のフォーマット ファイルと呼ばれます。 通常、既定のフォーマット ファイルでは、データ ファイル フィールドとテーブル列の間の 1 対 1 の対応が記述されます。

重要

接続するサーバー インスタンスの名前を指定する必要がある場合があります。 また、ユーザー名とパスワードを指定する必要がある場合もあります。 詳細については、「 bcp Utility」を参照してください。

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

myTestSkipCol の既定の XML 以外のフォーマット ファイル ()

注

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

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

  • または、テーブル列をスキップするために、テーブル列に対応するフォーマット ファイル行の定義を変更することもできます。 このフォーマット ファイル行では、"prefix length"、"host file data length"、および "server column order" の値を 0 に設定する必要があります。 また、"terminator" フィールドと "column collation" フィールドは "" (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 以外のフォーマット ファイルのいずれかを使用して動作します。 この例では、変更されたフォーマット ファイルの名前は C:\myTestSkipCol2.fmt です。 BULK INSERTを使用してmyTestSkipCol2.dat データ ファイルを一括インポートするには、SQL Server Management Studio クエリ エディターで次のコードを実行します。

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

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

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

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

INSERT ...<列リスト> を選択して<列リスト>を OPENROWSET(BULK...) から取り込む。

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

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

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

結果の既定の XML 以外のフォーマット ファイルでは、次のように、データ ファイル フィールドとテーブル列の 1 対 1 の対応が記述されます。

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

例示

このセクションの例では、 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 AdventureWorks2012;
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 AdventureWorks2012;
GO
BULK INSERT v_myTestSkipCol
FROM 'C:\myTestSkipCol2.dat'
WITH (FORMATFILE='C:\myTestSkipCol2.xml');
GO

こちらもご覧ください

bcp ユーティリティBULK INSERT (Transact-SQL)OPENROWSET (Transact-SQL)フォーマット ファイルを使用してデータ フィールドをスキップする (SQL Server)フォーマット ファイルを使用してテーブル列を Data-File フィールドにマップする (SQL Server)フォーマット ファイルを使用してデータを一括インポートする (SQL Server)

  • Last updated on