フォーマット ファイルを使用したテーブル列のスキップ (SQL Server)Use a Format File to Skip a Table Column (SQL Server)

適用対象: ○SQL Server ○Azure SQL Database ○Azure SQL Data Warehouse ○Parallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

この記事では、スキップされた列のデータがソース データ ファイルに存在しない場合に、フォーマット ファイルを使用してテーブル列のインポートをスキップする方法について説明します。This article describes how to use a format file to skip importing a table column when the data for the skipped column does not exist in the source data file. データ ファイルに含めることができるフィールドの数はインポート先のテーブルの列の数よりも少なくなります。つまり、インポート先のテーブルで次の 2 つの条件のうち少なくともどちらかが true である場合のみ、列のインポートをスキップすることができます。A data file can contain fewer fields than the number of columns in the destination table - that is, you can skip importing a column - only if at least one of the following two conditions is true in the destination table:

  • スキップされる列が NULL 値である。The skipped column is nullable.
  • スキップされる列に既定値が含まれている。The skipped column has a default value.

サンプル テーブルとデータ ファイルSample table and data file

この記事の例は、dbo スキーマにある myTestSkipCol という名前のテーブルを想定しています。The examples in this article expect a table named myTestSkipCol under the dbo schema. このテーブルは WideWorldImportersAdventureWorks などのサンプル データベース、またはその他の任意のデータベースで作成できます。You can create this table in a sample database such as WideWorldImporters or AdventureWorks or in any other database. このテーブルは次のように作成します。Create this table as follows:

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

この記事の例では、サンプル データ ファイル myTestSkipCol2.dat も使用します。The examples in this article also use a sample data file, myTestSkipCol2.dat. インポート先のテーブルには 3 つの列が含まれていますが、このデータ ファイルには 2 つのフィールドしか含まれていません。This data file contains only two fields, although the destination table contains three columns.

1,DataForColumn3  
1,DataForColumn3  
1,DataForColumn3  

基本的な手順Basic steps

XML 以外のフォーマット ファイルまたは XML フォーマット ファイルを使用して、テーブル列をスキップすることができます。You can use a non-XML format file or an XML format file to skip a table column. どちらの場合も、次の 2 つの手順を行います。In both cases, there are two steps:

  1. bcp コマンドライン ユーティリティを使用して、既定のフォーマット ファイルを作成します。Use the bcp command-line utility to create a default format file.

  2. テキスト エディターで、既定のフォーマット ファイルを変更します。Modify the default format file in a text editor.

フィードバックをお待ちしております。 この記事の手順やコード例の中で、古い情報や間違っている情報を見つけた場合は、ぜひお知らせください。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. このページの下部にある [フィードバック] セクション内で [このページ] ボタンをクリックしてください。You can click the This page button in the Feedback section at the bottom of this page. SQL に関するフィードバックのすべての項目に目を通しています (通常は翌日)。We read every item of feedback about SQL, typically the next day. よろしくお願いいたします。Thanks.

変更後のフォーマット ファイルで、存在するそれぞれのフィールドをインポート先のテーブルの対応する列にマップする必要があります。The modified format file must map each existing field to its corresponding column in the destination table. どのテーブル列 (複数可) をスキップするかも指定する必要があります。It must also indicate which table column or columns to skip.

たとえば、myTestSkipCol2.dat から myTestSkipCol テーブルにデータを一括インポートするには、フォーマット ファイルで最初のデータ フィールドを Col1 にマップし、Col2 をスキップして、2 番目のフィールドを Col3 にマップする必要があります。For example, to bulk import data from myTestSkipCol2.dat into the myTestSkipCol table, the format file must map the first data field to Col1, skip Col2, and map the second field to Col3.

オプション #1 - XML 以外のフォーマット ファイルの使用Option #1 - Use a non-XML format file

手順 #1 - XML 以外の既定のフォーマット ファイルの作成Step #1 - Create a default non-XML format file

コマンド プロンプトで次の bcp コマンドを実行して、myTestSkipCol サンプル テーブル用に作成した、XML 以外の既定のフォーマット ファイルを作成します。Create a default non-XML format file for the myTestSkipCol sample table by running the following bcp command at the command prompt:

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

重要

場合によっては、-S 引数で接続先サーバー インスタンスの名前を指定する必要があります。You might have to specify the name of the server instance to which you are connecting with the -S argument. また、-U-P の引数を使用したユーザー名とパスワードの指定が必要な場合もあります。Also, you might have to specify the user name and password with the -U and -P arguments. 詳細については、「 bcp Utility」を参照してください。For more information, see bcp Utility.

上記のコマンドでは、 myTestSkipCol_Default.fmtという XML 以外のフォーマット ファイルを作成します。The previous command creates a non-XML format file, myTestSkipCol_Default.fmt. このフォーマット ファイルは bcp で作成した形式なので、 既定のフォーマット ファイルといいます。This format file is called a default format file because it is the form generated by bcp. 既定のフォーマット ファイルには、データ ファイル フィールドとテーブル列の一対一の対応が記述されます。A default format file describes a one-to-one correspondence between data-file fields and table columns.

次のスクリーンショットに、この既定のフォーマット ファイルのサンプルで使用されている値を示します。The following screenshot shows the values in this sample default format files.

myTestSkipCol 用の既定の XML 以外のフォーマット ファイルdefault non-XML format file for myTestSkipCol

注意

フォーマット ファイル フィールドの詳細については、「XML 以外のフォーマット ファイル (SQL Server)」をご覧ください。For more information about the format-file fields, see Non-XML Format Files (SQL Server).

手順 #2 - XML 以外のフォーマット ファイルの変更Step #2 - Modify a non-XML format file

XML 以外の既定のフォーマット ファイルを変更するには、2 つの別の方法があります。To modify a default non-XML format file, there are two alternatives. どちらの方法も、データ ファイルにデータ フィールドが存在しないこと、および対応するテーブル列にデータが挿入されないことを示します。Either alternative indicates that the data field does not exist in the data file and that no data is to be inserted into the corresponding table column.

テーブル列をスキップするには、既定の XML 以外のフォーマット ファイルを編集し、そのファイルを以下のいずれかの方法で変更します。To skip a table column, edit the default non-XML format file and modify the file by using one of the following alternative methods:

オプション #1 - 行の削除Option #1 - Remove the row

列のスキップに推奨されている方法には、次の 3 つの手順が含まれています。The preferred method for skipping a column involves the following three steps:

  1. まず、ソース データ ファイルに存在しないフィールドを記述しているフォーマット ファイルの行をすべて削除します。First, delete any format-file row that describes a field that is missing from the source data file.
  2. 次に、削除したフォーマット ファイルの行に続く各行の "ホスト ファイル フィールドの順序" の値を減らします。Then, reduce the "Host file field order" value of each format-file row that follows a deleted row. "ホスト ファイル フィールドの順序" の値が、データ ファイル内での各データ フィールドの実際の場所を反映した 1 ~ nの通し番号になるようにします。The goal is sequential "Host file field order" values, 1 through n, that reflect the actual position of each data field in the data file.
  3. 最後に、データ ファイル内の実際のフィールド数が反映されるように、"列の数" フィールドの値を減らします。Finally, reduce the value in the "Number of columns" field to reflect the actual number of fields in the data file.

次の例は、myTestSkipCol テーブルの既定のフォーマット ファイルに基づいています。The following example is based on the default format file for the myTestSkipCol table. 変更後のフォーマット ファイルは、最初のデータ フィールドを Col1にマップし、 Col2をスキップして、2 番目のデータ フィールドを Col3にマップしています。This modified format file maps the first data field to Col1, skips Col2, and maps the second data field to Col3. Col2 に相当する行は削除されています。The row for Col2 has been deleted. 最初のフィールドの後ろの区切り記号も \t から , に変更されています。The delimiter after the first field has also been changed from \t to ,.

14.0  
2  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS  

オプション #2 - 行定義の変更Option #2 - Modify the row definition

スキップするテーブル列に対応するフォーマット ファイルの行の定義を変更することもできます。Alternatively, to skip a table column, you can modify the definition of the format-file row that corresponds to the table column. 変更するフォーマット ファイルの行は、"プレフィックス長"、"ホスト ファイルのデータ長"、および "サーバーの列の順序" の値を 0 に設定する必要があります。In this format-file row, the "prefix length," "host file data length," and "server column order" values must be set to 0. さらに、"ターミネータ" および "列の照合順序" のフィールドを "" (つまり、空または NULL 値) に設定する必要があります。Also, the "terminator" and "column collation" fields must be set to "" (that is, to an empty or NULL value). "サーバーの列名" の値は、実際の列名が不要な場合でも、空でない文字列を指定する必要があります。The "server column name" value requires a non-blank string, though the actual column name is not necessary. 残りのフォーマット フィールドは既定値のままにしておく必要があります。The remaining format fields require their default values.

次の例も、 myTestSkipCol テーブルの既定のフォーマット ファイルから作成しました。The following example is also derived from the default format file for the myTestSkipCol table.

14.0  
3  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       0       ""       0     Col2         ""  
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS  

XML 以外のフォーマット ファイルの例Examples with a non-XML format file

以下の例は、この記事で既に説明した myTestSkipCol サンプル テーブルおよび myTestSkipCol2.dat サンプル データ ファイルに基づいています。The following examples are based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are described earlier in this article.

BULK INSERT の使用Using BULK INSERT

この例は、前のセクションの説明のとおりに作成した変更済みの XML 以外のフォーマット ファイルのいずれかを使用することで動作します。This example works by using either of the modified non-XML format files created as described in the preceding section. この例では、変更後のフォーマット ファイルの名前を myTestSkipCol2.fmtとします。In this example, the modified format file is named myTestSkipCol2.fmt. BULK INSERT を使用して myTestSkipCol2.dat データ ファイルを一括インポートするには、SSMS で次のコードを実行します。To use BULK INSERT to bulk import the myTestSkipCol2.dat data file, in SSMS, run the following code. お使いのコンピューターのサンプル ファイルがある場所のファイル システム パスを更新します。Update the file system paths for the location of the sample files on your computer.

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

オプション #2 - XML フォーマット ファイルの使用Option #2 - Use an XML format file

手順 #1 - 既定の XML フォーマット ファイルの作成Step #1 - Create a default XML format file

コマンド プロンプトで次の bcp コマンドを実行して、myTestSkipCol サンプル テーブル用に作成した、既定の XML フォーマット ファイルを作成します。Create a default XML format file for the myTestSkipCol sample table by running the following bcp command at the command prompt:

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

重要

場合によっては、-S 引数で接続先サーバー インスタンスの名前を指定する必要があります。You might have to specify the name of the server instance to which you are connecting with the -S argument. また、-U-P の引数を使用したユーザー名とパスワードの指定が必要な場合もあります。Also, you might have to specify the user name and password with the -U and -P arguments. 詳細については、「 bcp Utility」を参照してください。For more information, see bcp Utility.

上記のコマンドでは、myTestSkipCol_Default.xml という XML フォーマット ファイルを作成します。The previous command creates an XML format file, myTestSkipCol_Default.xml. このフォーマット ファイルは bcp で作成した形式なので、 既定のフォーマット ファイルといいます。This format file is called a default format file because it is the form generated by bcp. 既定のフォーマット ファイルには、データ ファイル フィールドとテーブル列の一対一の対応が記述されます。A default format file describes a one-to-one correspondence between data-file fields and table columns.

<?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)」をご覧ください。For information about the structure of XML format files, see XML Format Files (SQL Server).

手順 #2 - XML フォーマット ファイルの変更Step #2 - Modify an XML format file

これは、Col2 をスキップする、変更済みの XML フォーマット ファイル myTestSkipCol2.xml です。Here is the modified XML format file, myTestSkipCol2.xml, which skips Col2. Col2FIELDROW のエントリが削除され、エントリの番号が変更されています。The FIELD and ROW entries for Col2 have been removed and the entries have been renumbered. 最初のフィールドの後ろの区切り記号も \t から , に変更されています。The delimiter after the first field has also been changed from \t to ,.

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

XML フォーマット ファイルの例Examples with an XML format file

以下の例は、この記事で既に説明した myTestSkipCol サンプル テーブルおよび myTestSkipCol2.dat サンプル データ ファイルに基づいています。The following examples are based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are described earlier in this article.

myTestSkipCol2.dat から myTestSkipCol テーブルにデータをインポートするため、この例では変更した XML フォーマット ファイル myTestSkipCol2.xml を使用します。To import the data from myTestSkipCol2.dat into the myTestSkipCol table, the examples use the modified XML format file, myTestSkipCol2.xml.

ビューでの BULK INSERT の使用Using BULK INSERT with a view

XML フォーマット ファイルでは、bcp コマンドまたは BULK INSERT ステートメントを使用して直接テーブルにインポートする場合は、列をスキップできません。With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. ただし、テーブルの最後の列を除くすべての列にインポートできます。However, you can import into all but the last column of a table. 最後の列を除く任意の列をスキップする必要がある場合、データ ファイルに含まれている列のみを含んでいる対象テーブルのビューを作成する必要があります。If you have to skip any column other than the last column, you must create a view of the target table that contains only the columns contained in the data file. その後、データ ファイルからビューにデータを一括インポートできます。Then, you can bulk import data from that file into the view.

次の例では、myTestSkipCol テーブルに v_myTestSkipCol ビューを作成します。The following example creates the v_myTestSkipCol view on the myTestSkipCol table. このビューでは 2 番目のテーブル列である Col2がスキップされます。This view skips the second table column, Col2. その後、 BULK INSERT を使用して myTestSkipCol2.dat データ ファイルをこのビューにインポートします。The example then uses BULK INSERT to import the myTestSkipCol2.dat data file into this view.

SSMS で、次のコードを実行します。In SSMS, run the following code. お使いのコンピューターのサンプル ファイルがある場所のファイル システム パスを更新します。Update the file system paths for the location of the sample files on your computer.

USE WideWorldImporters;  
GO  

CREATE VIEW v_myTestSkipCol AS  
    SELECT Col1,Col3  
    FROM myTestSkipCol;  
GO  
  
BULK INSERT v_myTestSkipCol  
FROM 'C:\myTestSkipCol2.dat'  
WITH (FORMATFILE='C:\myTestSkipCol2.xml');  
GO  

OPENROWSET(BULK...) の使用Using OPENROWSET(BULK...)

OPENROWSET(BULK...) を使用してテーブル列をスキップするために XML フォーマット ファイルを使用するには、次のように選択リストおよび対象テーブルの列リストを明示的に指定する必要があります。To use an XML format file to skip a table column by using OPENROWSET(BULK...), you have to provide an explicit list of columns in the select list and also in the target table, as follows:

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

次の例では、 OPENROWSET 一括行セット プロバイダーと myTestSkipCol2.xml フォーマット ファイルを使用します。The following example uses the OPENROWSET bulk rowset provider and the myTestSkipCol2.xml format file. この例では、 myTestSkipCol2.dat データ ファイルを myTestSkipCol テーブルに一括インポートします。The example bulk imports the myTestSkipCol2.dat data file into the myTestSkipCol table. 必要に応じて、ステートメントでは、選択リストおよびターゲット テーブルの列の一覧を明示的に指定します。The statement contains an explicit list of columns in the select list and also in the target table, as required.

SSMS で、次のコードを実行します。In SSMS, run the following code. お使いのコンピューターのサンプル ファイルがある場所のファイル システム パスを更新します。Update the file system paths for the location of the sample files on your computer.

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

参照See Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server) Use a Format File to Skip a Data Field (SQL Server)
フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server) Use a Format File to Map Table Columns to Data-File Fields (SQL Server)
データの一括インポートでのフォーマット ファイルの使用 (SQL Server)Use a Format File to Bulk Import Data (SQL Server)