フォーマットファイルを使用したテーブル列とデータファイルフィールドのマッピング (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 ステートメントを使用してデータ移動を実行します。

テスト条件の例

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

サンプルテーブル

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

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myRemap
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   Gender char(1)
   );

サンプルデータファイル

以下のデータは、 FirstName と LastName をテーブル myRemapと逆の順序で示しています。メモ帳を使用して、空のファイル D:\BCP\myRemap.bcp を作成し、次のデータを挿入します。

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

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

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

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

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

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

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

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

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

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

行が myRemap.bcpのデータと同じ順序になるようにフォーマットファイルの行の順序を配置し直します。
ホストファイルフィールドの順序の値が順番になっていることを確認します。
最後のフォーマットファイル行の後にキャリッジリターンがあることを確認します。

変更内容を比較します。

変更前

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

変更後

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

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

myRemap.bcp の最初のデータフィールドは最初の列にマップされます: myRemap.. PersonID
myRemap.bcp の 2 番目のデータフィールドは 3 番目の列にマップされます: myRemap.. LastName
myRemap.bcp の 3 番目のデータフィールドは 2 番目の列にマップされます: myRemap.. FirstName
myRemap.bcp の 4 番目のデータフィールドは 4 番目の列にマップされます: myRemap.. Gender

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

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

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

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

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

フォーマットファイルで <FIELD> 要素が宣言される順序は、データファイルでフィールドが表示される順序と同じです。したがって、ID 属性 2 と 3 を使用して <FIELD> 要素の順序を逆にします。
<FIELD> ID 属性値が順番になっていることを確認します。
<ROW> 要素内の <COLUMN> 要素の順序により、一括操作でそれらがターゲットに送信される順序が定義されます。 XML フォーマットファイルでは、一括インポート操作の対象になるテーブルの列とのリレーションシップがない各 <COLUMN> 要素にローカル名が割り当てられます。 <COLUMN> 要素の順序は、<RECORD> 定義の <FIELD> 要素の順序とは関係ありません。各 <COLUMN> 要素は、<FIELD> 要素 (その ID は、<COLUMN> 要素の SOURCE 属性で指定されます) に対応しています。このため、<COLUMN> SOURCE の値は、リビジョンを必要とする属性のみとなります。 <COLUMN> SOURCE 属性 2 および 3 の順番を逆にします。

変更内容を比較します。

変更前

<?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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" 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"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</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="30" 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="1" 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="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

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

COLUMN 1 に対応する FIELD 1 は、最初のテーブル列にマップされます: myRemap.. PersonID
COLUMN 2 に対応する FIELD 2 は、3 番目のテーブル列に再マップされます: myRemap.. LastName
COLUMN 3 に対応する FIELD 3 は、2 番目のテーブル列に再マップされます: myRemap.. FirstName
COLUMN 4 に対応する FIELD 4 は、4 番目のテーブル列にマップされます: myRemap.. Gender

フォーマットファイルを使用してデータをインポートし、テーブル列をデータファイルフィールドにマップする

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

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

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

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

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

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

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

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

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

USE TestDatabase;  
GO

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

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

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

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

USE TestDatabase;  
GO

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

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

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

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

USE TestDatabase;
GO

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

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

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

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

USE TestDatabase;  
GO

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

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

フォーマットファイルを使用したテーブル列とデータファイルフィールドのマッピング (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 フォーマットファイルを使用する