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

[アーティクル]
2017-06-13

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

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

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

USE AdventureWorks2012;
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にマップし、 Col3をスキップして 2 番目のフィールドを Col2にマップする必要があります。

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

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

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

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

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

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

重要

場合によっては、接続先サーバーインスタンスの名前を指定する必要があります。また、ユーザー名とパスワードの指定が必要な場合もあります。詳細については、「 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
```
スキップするテーブル列に対応するフォーマットファイルの行の定義を変更することもできます。変更するフォーマットファイルの行は、"プレフィックス長"、"ホストファイルのデータ長"、および "サーバーの列の順序" の値を 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 AdventureWorks2012;
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 フォーマットファイルを使用するには、次のように選択リストおよび対象テーブルの列リストを明示的に指定する必要があります。

挿入。。。<> 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 フォーマットファイルには、次に示すように、データファイルフィールドとテーブル列の一対一の対応が記述されます。

<?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)

次の方法で共有

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

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

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

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

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

例