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

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

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

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

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 以外のフォーマットファイルの作成

このトピックでは、次の 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...<COLUMN_LIST SELECT column_list FROM 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 の使用

次の例では、v_myTestSkipCol テーブルに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 2017-06-13

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

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

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

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

XML 以外のフォーマット ファイルを変更するためのメソッド

例示