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

[アーティクル]
04/04/2023

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

この記事では、スキップされた列のデータがソースデータファイルに存在しない場合に、フォーマットファイルを使用してテーブル列のインポートをスキップする方法について説明します。データファイルに含めることができるフィールドの数はインポート先のテーブルの列の数よりも少なくなります。つまり、インポート先のテーブルで次の 2 つの条件のうち少なくともどちらかが true である場合のみ、列のインポートをスキップすることができます。

スキップされる列が NULL 値である。
スキップされる列に既定値が含まれている。

Note

一括挿入を含むこの構文は、Azure Synapse Analytics ではサポートされていません。 Azure Synapse Analytics やその他のクラウドデータベースプラットフォーム統合では、Azure Data Factory の COPY ステートメントを介して、または COPY INTO や PolyBase などの T-SQL ステートメントを使用してデータ移動を実行します。

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

この記事の例は、dbo スキーマにある myTestSkipCol という名前のテーブルを想定しています。このテーブルは WideWorldImporters や AdventureWorks などのサンプルデータベース、またはその他の任意のデータベースで作成できます。このテーブルは次のように作成します。

USE WideWorldImporters;  
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

基本的な手順

XML 以外のフォーマットファイルまたは XML フォーマットファイルを使用して、テーブル列をスキップすることができます。どちらの場合も、次の 2 つの手順を行います。

bcp コマンドラインユーティリティを使用して、既定のフォーマットファイルを作成します。
テキストエディターで、既定のフォーマットファイルを変更します。

変更後のフォーマットファイルで、存在するそれぞれのフィールドをインポート先のテーブルの対応する列にマップする必要があります。どのテーブル列 (複数可) をスキップするかも指定する必要があります。

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

オプション #1 - XML 以外のフォーマットファイルの使用

手順 #1 - XML 以外の既定のフォーマットファイルの作成

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

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

重要

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

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

次のスクリーンショットに、この既定のフォーマットファイルのサンプルで使用されている値を示します。

mytestskipcol 用の既定の XML 以外のフォーマットファイルを詳しく示す図。

Note

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

手順 #2 - XML 以外のフォーマットファイルの変更

XML 以外の既定のフォーマットファイルを変更するには、2 つの別の方法があります。どちらの方法も、データファイルにデータフィールドが存在しないこと、および対応するテーブル列にデータが挿入されないことを示します。

テーブル列をスキップするには、既定の XML 以外のフォーマットファイルを編集し、そのファイルを以下のいずれかの方法で変更します。

オプション #1 - 行の削除

列のスキップに推奨されている方法には、次の 3 つの手順が含まれています。

まず、ソースデータファイルに存在しないフィールドを記述しているフォーマットファイルの行をすべて削除します。
次に、削除したフォーマットファイルの行に続く各行の "ホストファイルフィールドの順序" の値を減らします。 "ホストファイルフィールドの順序" の値が、データファイル内での各データフィールドの実際の場所を反映した 1 ～ nの通し番号になるようにします。
最後に、データファイル内の実際のフィールド数が反映されるように、"列の数" フィールドの値を減らします。

次の例は、myTestSkipCol テーブルの既定のフォーマットファイルに基づいています。変更後のフォーマットファイルは、最初のデータフィールドを Col1にマップし、 Col2をスキップして、2 番目のデータフィールドを Col3にマップしています。 Col2 に相当する行は削除されています。最初のフィールドの後ろの区切り記号も \t から , に変更されています。

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

オプション #2 - 行定義の変更

スキップするテーブル列に対応するフォーマットファイルの行の定義を変更することもできます。変更するフォーマットファイルの行は、"プレフィックス長"、"ホストファイルのデータ長"、および "サーバーの列の順序" の値を 0 に設定する必要があります。さらに、"ターミネータ" および "列の照合順序" のフィールドを "" (つまり、空または NULL 値) に設定する必要があります。 "サーバーの列名" の値は、実際の列名が不要な場合でも、空でない文字列を指定する必要があります。残りのフォーマットフィールドは既定値のままにしておく必要があります。

次の例も、 myTestSkipCol テーブルの既定のフォーマットファイルから作成しました。

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

以下の例は、この記事で既に説明した myTestSkipCol サンプルテーブルおよび myTestSkipCol2.dat サンプルデータファイルに基づいています。

BULK INSERT を使用する

この例は、前のセクションの説明のとおりに作成した変更済みの XML 以外のフォーマットファイルのいずれかを使用することで動作します。この例では、変更後のフォーマットファイルの名前を myTestSkipCol2.fmtとします。 BULK INSERT を使用して myTestSkipCol2.dat データファイルを一括インポートするには、SQL Server Management Studio (SSMS) で次のコードを実行します。お使いのコンピューターのサンプルファイルがある場所のファイルシステムパスを更新します。

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

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

手順 #1 - 既定の XML フォーマットファイルの作成

コマンドプロンプトで次の bcp コマンドを実行して、myTestSkipCol サンプルテーブル用に作成した、既定の XML フォーマットファイルを作成します。

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

重要

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

<?xml version="1.0"?>  
<BCPFORMAT xmlns="http://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>

Note

XML フォーマットファイルの構造に関する詳細については、「XML フォーマットファイル (SQL Server)」を参照してください。

手順 #2 - XML フォーマットファイルの変更

これは、Col2 をスキップする、変更済みの XML フォーマットファイル myTestSkipCol2.xml です。 Col2 の FIELD と ROW のエントリが削除され、エントリの番号が変更されています。最初のフィールドの後ろの区切り記号も \t から , に変更されています。

<?xml version="1.0"?>  
<BCPFORMAT xmlns="http://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 フォーマットファイルの例

以下の例は、この記事で既に説明した myTestSkipCol サンプルテーブルおよび myTestSkipCol2.dat サンプルデータファイルに基づいています。

myTestSkipCol2.dat から myTestSkipCol テーブルにデータをインポートするため、この例では変更した XML フォーマットファイル myTestSkipCol2.xml を使用します。

ビューで BULK INSERT を使用する

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

次の例では、myTestSkipCol テーブルに v_myTestSkipCol ビューを作成します。このビューでは 2 番目のテーブル列である Col2がスキップされます。その後、 BULK INSERT を使用して myTestSkipCol2.dat データファイルをこのビューにインポートします。

SSMS で、次のコードを実行します。お使いのコンピューターのサンプルファイルがある場所のファイルシステムパスを更新します。

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...) を使用する

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

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

次の例では、 OPENROWSET 一括行セットプロバイダーと myTestSkipCol2.xml フォーマットファイルを使用します。この例では、 myTestSkipCol2.dat データファイルを myTestSkipCol テーブルに一括インポートします。必要に応じて、ステートメントでは、選択リストおよびターゲットテーブルの列の一覧を明示的に指定します。

SSMS で、次のコードを実行します。お使いのコンピューターのサンプルファイルがある場所のファイルシステムパスを更新します。

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

次の方法で共有

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

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

基本的な手順

オプション #1 - XML 以外のフォーマットファイルの使用

手順 #1 - XML 以外の既定のフォーマットファイルの作成

手順 #2 - XML 以外のフォーマットファイルの変更

オプション #1 - 行の削除

オプション #2 - 行定義の変更

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

BULK INSERT を使用する

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

手順 #1 - 既定の XML フォーマットファイルの作成

手順 #2 - XML フォーマットファイルの変更

XML フォーマットファイルの例

ビューで BULK INSERT を使用する

OPENROWSET(BULK...) を使用する

次のステップ

フィードバック

その他のリソース

次の方法で共有

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

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

基本的な手順

オプション #1 - XML 以外のフォーマット ファイルの使用

手順 #1 - XML 以外の既定のフォーマット ファイルの作成

手順 #2 - XML 以外のフォーマット ファイルの変更

オプション #1 - 行の削除

オプション #2 - 行定義の変更

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

BULK INSERT を使用する

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

手順 #1 - 既定の XML フォーマット ファイルの作成

手順 #2 - XML フォーマット ファイルの変更

XML フォーマット ファイルの例

ビューで BULK INSERT を使用する

OPENROWSET(BULK...) を使用する

次のステップ

フィードバック

その他のリソース

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

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

オプション #1 - XML 以外のフォーマットファイルの使用

手順 #1 - XML 以外の既定のフォーマットファイルの作成

手順 #2 - XML 以外のフォーマットファイルの変更

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

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

手順 #1 - 既定の XML フォーマットファイルの作成

手順 #2 - XML フォーマットファイルの変更

XML フォーマットファイルの例