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

  • [アーティクル]

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

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

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

Note

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

テスト条件の例

この記事の変更されたフォーマット ファイルの例は、この記事の後半で定義されているテーブルとデータ ファイルに基づいています。

サンプル テーブル

次の Transact-SQL スクリプトは、テスト データベースと myRemap という名前のテーブルを作成します。

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

サンプル データ ファイル

次のデータは、テーブル myRemapに示すように、FirstNameとLastNameを逆の順序で表示します。 メモ帳などのテキスト エディターを使用して、空のファイル 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のスキーマに基づいて XML 以外のフォーマット ファイル (myRemap.fmt) を生成します。 さらに、修飾子 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 フォーマット ファイルmyRemap.xmlを作成します。 さらに、修飾子 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> 要素に対応しています (この要素の 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 以外のフォーマット ファイルを使用する

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

次の 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 および XML 以外のフォーマット ファイルを使用する

次の 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 および XML フォーマット ファイルを使用する

次の 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;

関連するコンテンツ