フォーマットファイルを使用したデータフィールドのスキップ (SQL Server)

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

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

データファイルには、テーブルの列数よりも多くのフィールドを格納できます。このトピックでは、XML 以外のフォーマットファイルと XML フォーマットファイルの両方を変更し、データファイルに多くのフィールドを格納する方法について説明します。この操作は、テーブル列を対応するデータフィールドにマップし、余分なフィールドを無視することによって行います。詳細については、「フォーマットファイルの作成 (SQL Server) 」を参照してください。

注意

XML 以外のフォーマットファイルまたは XML フォーマットファイルを使用して、データファイルをテーブルに一括インポートできます。その場合、bcp ユーティリティコマンド、BULK INSERT ステートメント、または INSERT ...SELECT * FROM OPENROWSET(BULK...) ステートメント。詳細については、「データの一括インポートでのフォーマットファイルの使用 (SQL Server)」を参照してください。

注意

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

テスト条件の例

このトピックの変更するフォーマットファイルの例は、以下に定義されたテーブルとデータファイルに基づいています。

サンプルテーブル

以下のスクリプトでは、テストデータベースと myTestSkipFieldという名前のテーブルが作成されます。 Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

サンプルデータファイル

空のファイル D:\BCP\myTestSkipField.bcp を作成し、次のデータを挿入します。

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

フォーマットファイルを作成する

myTestSkipField.bcp から myTestSkipField テーブルにデータを一括インポートするには、フォーマットファイルで次の操作を行う必要があります。

最初のデータフィールドを最初の列 PersonIDにマップします。
2 番目のデータフィールドをスキップします。
3 番目のデータフィールドを 2 番目の列 FirstNameにマップします。
4 番目のデータフィールドを 3 番目の列 LastNameにマップします。

フォーマットファイルを作成する最も簡単な方法は、 bcp ユーティリティを使用することです。最初に、既存のテーブルからベースフォーマットファイルを作成します。次に、実際のデータファイルを反映するようにベースフォーマットファイルを変更します。

XML 以外のフォーマットファイルを作成する

詳細については、「XML 以外のフォーマットファイル (SQL Server)」を参照してください。次のコマンドでは、 bcp ユーティリティを使用し、 myTestSkipField.fmtのスキーマに基づいて XML 以外のフォーマットファイル myTestSkipFieldを生成します。さらに、修飾子 c を使用して文字データを指定し、 t, を使用してフィールドターミネータとしてコンマを指定し、 T を使用して統合セキュリティによる信頼された接続を指定します。コマンドプロンプトで、次のコマンドを入力します。

bcp TestDatabase.dbo.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

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

用語については、「XML 以外のフォーマットファイルの構造」を参照してください。メモ帳で D:\BCP\myTestSkipField.fmt を開き、次のように変更します。

FirstName のフォーマットファイル行全体をコピーし、次の行の FirstName のすぐ後ろに貼り付けます。
新しい行とすべての後続行のホストファイルフィールドの順序の値を 1 増やします。
データファイル内の実際のフィールド数が反映されるように、列数の値を増やします。
2 番目のフォーマットファイル行のサーバー列の順序を 2 から 0 に変更します。

加えた変更を比較します。

変更前

13.0
3
1       SQLCHAR    0       7       ","      1     PersonID        ""
2       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       30      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

変更後

13.0
4
1       SQLCHAR    0       7       ","      1     PersonID     ""
2       SQLCHAR    0       25      ","      0     FirstName    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       50      "\r\n"   3     LastName     SQL_Latin1_General_CP1_CI_AS

変更されたフォーマットファイルは次のように反映されます。

データフィールドが 4 つになります。
myTestSkipField.bcp の最初のデータフィールドは最初の列にマップされます: myTestSkipField.. PersonID
myTestSkipField.bcp の 2 番目のデータフィールドはどの列にもマップされません。
myTestSkipField.bcp の 3 番目のデータフィールドは 2 番目の列にマップされます: myTestSkipField.. FirstName
myTestSkipField.bcp の 4 番目のデータフィールドは 3 番目の列にマップされます: myTestSkipField.. LastName

XML フォーマットファイルを使用する

詳細については、「XML フォーマットファイル (SQL Server)」を参照してください。次のコマンドでは、 bcp ユーティリティを使用し、 myTestSkipField.xmlのスキーマに基づいて XML のフォーマットファイル myTestSkipFieldを生成します。さらに、修飾子 c を使用して文字データを指定し、 t, を使用してフィールドターミネータとしてコンマを指定し、 T を使用して統合セキュリティによる信頼された接続を指定します。 XML ベースのフォーマットファイルを生成する場合は、 x 修飾子を使用する必要があります。コマンドプロンプトで、次のコマンドを入力します。

bcp TestDatabase.dbo.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

XML フォーマットファイルを変更する

用語については、「XML フォーマットファイルのスキーマ構文」を参照してください。メモ帳で D:\BCP\myTestSkipField.xml を開き、次のように変更します。

2 番目のフィールド全体をコピーし、次の行の 2 番目のフィールドのすぐ後ろに貼り付けます。
新しい FIELD と後続の各 FIELD の "FIELD ID" 値を 1 増やします。
FirstNameと LastName の "COLUMN SOURCE" 値を 1 増やし、変更されたマッピングを反映するようにします。

加えた変更を比較します。

変更前

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

変更後

<?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="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

変更されたフォーマットファイルは次のように反映されます。

データフィールドが 4 つになります。
COLUMN 1 に対応する FIELD 1 は、最初のテーブル列にマップされます: myTestSkipField.. PersonID
FIELD 2 はどの COLUMN にも対応していないため、どのテーブル列にもマップされません。
COLUMN 3 に対応する FIELD 3 は、2 番目のテーブル列にマップされます: myTestSkipField.. FirstName
COLUMN 4 に対応する FIELD 4 は、3 番目のテーブル列にマップされます: myTestSkipField.. LastName

データフィールドをスキップするためのフォーマットファイルを使用してデータをインポートする

次の例では、データベース、データファイル、および上記で作成したフォーマットファイルを使用します。

bcp と XML 以外のフォーマットファイルを使用する

コマンドプロンプトで、次のコマンドを入力します。

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

bcp と XML フォーマットファイルを使用する

コマンドプロンプトで、次のコマンドを入力します。

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

BULK INSERT と XML 以外のフォーマットファイルを使用する

Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

BULK INSERT と XML フォーマットファイルを使用する

Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
BULK INSERT dbo.myTestSkipField  
   FROM 'D:\BCP\myTestSkipField.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

OPENROWSET(BULK...) と XML 以外のフォーマットファイルを使用する

Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

USE TestDatabase;
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

OPENROWSET(BULK...) と XML フォーマットファイルを使用する

Microsoft SQL Server Management Studio (SSMS) で次の Transact-SQL を実行します。

USE TestDatabase;  
GO

TRUNCATE TABLE myTestSkipField;
INSERT INTO dbo.myTestSkipField  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;

フォーマットファイルを使用したデータフィールドのスキップ (SQL Server)

テスト条件の例

サンプルテーブル

サンプルデータファイル

フォーマットファイルを作成する

XML 以外のフォーマットファイルを作成する

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

XML フォーマットファイルを使用する

XML フォーマットファイルを変更する

データフィールドをスキップするためのフォーマットファイルを使用してデータをインポートする

bcp と XML 以外のフォーマットファイルを使用する

bcp と XML フォーマットファイルを使用する

BULK INSERT と XML 以外のフォーマットファイルを使用する

BULK INSERT と XML フォーマットファイルを使用する

OPENROWSET(BULK...) と XML 以外のフォーマットファイルを使用する

OPENROWSET(BULK...) と XML フォーマットファイルを使用する

次の手順

フィードバック

フィードバック

その他のリソース

フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)

テスト条件の例

サンプル テーブル

サンプル データ ファイル

フォーマット ファイルを作成する

XML 以外のフォーマット ファイルを作成する

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

XML フォーマット ファイルを使用する

XML フォーマット ファイルを変更する

データ フィールドをスキップするためのフォーマット ファイルを使用してデータをインポートする

bcp と XML 以外のフォーマット ファイルを使用する

bcp と XML フォーマット ファイルを使用する

BULK INSERT と XML 以外のフォーマット ファイルを使用する

BULK INSERT と XML フォーマット ファイルを使用する

OPENROWSET(BULK...) と XML 以外のフォーマット ファイルを使用する

OPENROWSET(BULK...) と XML フォーマット ファイルを使用する

次の手順

フィードバック

フィードバック

その他のリソース

フォーマットファイルを使用したデータフィールドのスキップ (SQL Server)

サンプルテーブル

サンプルデータファイル

フォーマットファイルを作成する

XML 以外のフォーマットファイルを作成する

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

XML フォーマットファイルを使用する

XML フォーマットファイルを変更する

データフィールドをスキップするためのフォーマットファイルを使用してデータをインポートする

bcp と XML 以外のフォーマットファイルを使用する

bcp と XML フォーマットファイルを使用する

BULK INSERT と XML 以外のフォーマットファイルを使用する

BULK INSERT と XML フォーマットファイルを使用する

OPENROWSET(BULK...) と XML 以外のフォーマットファイルを使用する

OPENROWSET(BULK...) と XML フォーマットファイルを使用する