フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)

適用対象:yesSQL Server (サポートされているすべてのバージョン) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Platform System (PDW)

データ ファイルに含めるフィールドは、対応するテーブル内の列とは異なる順序に並べ替えることができます。 このトピックでは、テーブル列とは異なる順序にフィールドを並べ替えたデータ ファイルを格納できるように変更した XML フォーマット ファイルと XML 以外のフォーマット ファイルについて説明します。 変更したフォーマット ファイルのデータ フィールドは、対応するテーブル列にマッピングされます。 詳細については、「 フォーマット ファイルの作成 (SQL Server) 」を参照してください。

[外枠]
テスト条件の例
 ● サンプル テーブル
 ● サンプル データ ファイル
フォーマット ファイルの作成
 ● XML 以外のフォーマット ファイルを作成する
 ● XML 以外のフォーマット ファイルの変更
 ● XML フォーマット ファイルの作成
 ● XML フォーマット ファイルの変更
フォーマット ファイルを使用してデータをインポートして、テーブル列をデータ ファイル フィールドにマッピングする
 ● bcp および XML 以外のフォーマット ファイルの使用
 ● bcp および XML フォーマット ファイルの使用
 ● BULK INSERT および XML 以外のフォーマット ファイルの使用
 ● BULK INSERT および XML フォーマット ファイルの使用
 ● OPENROWSET(BULK...) および XML 以外のフォーマット ファイルの使用
 ● OPENROWSET(BULK...) および XML フォーマット ファイルの使用

注意

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

テスト条件の例

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

サンプル テーブル

以下のスクリプトでは、テスト データベースと 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)
   );

サンプル データ ファイル

以下のデータは、 FirstNameLastName をテーブル 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 を開き、次のように変更します。

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

変更内容を比較します。
変更前

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 を開き、次のように変更します。

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

変更内容を比較します。
変更前

<?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="," 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="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="," 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

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

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

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

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

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

bcpXML フォーマット ファイルの使用

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

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

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

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 INSERTXML フォーマット ファイルの使用

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;

参照

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