フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

適用対象: ○SQL Server ○Azure SQL Database ○Azure SQL Data Warehouse ○Parallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

データ ファイルに含めるフィールドは、対応するテーブル内の列とは異なる順序に並べ替えることができます。A data file can contain fields arranged in a different order from the corresponding columns in the table. このトピックでは、テーブル列とは異なる順序にフィールドを並べ替えたデータ ファイルを格納できるように変更した XML フォーマット ファイルと XML 以外のフォーマット ファイルについて説明します。This topic presents both non-XML and XML format files that have been modified to accommodate a data file whose fields are arranged in a different order from the table columns. 変更したフォーマット ファイルのデータ フィールドは、対応するテーブル列にマッピングされます。The modified format file maps the data fields to their corresponding table columns. 詳細については、「 フォーマット ファイルの作成 (SQL Server) 」を参照してください。Please review Create a Format File (SQL Server) for additional information.

[外枠]Outline
テスト条件の例Example Test Conditions
 ● サンプル テーブル ● Sample Table
 ● サンプル データ ファイル ● Sample Data File
フォーマット ファイルの作成Creating the Format Files
 ● XML 以外のフォーマット ファイルの作成 ● Creating a Non-XML Format File
 ● XML 以外のフォーマット ファイルの変更 ● Modifying the Non-XML Format File
 ● XML フォーマット ファイルの作成 ● Creating an XML Format File
 ● XML フォーマット ファイルの変更 ● Modifying the XML Format File
フォーマット ファイルを使用してデータをインポートして、テーブル列をデータ ファイル フィールドにマッピングするImporting Data with a Format File to Map Table Columns to Data-File Field
 ● bcp と XML 以外のフォーマット ファイルの使用 ● Using bcp and Non-XML Format File
 ● bcp と XML フォーマット ファイルの使用 ● Using bcp and XML Format File
 ● BULK INSERT と XML 以外のフォーマット ファイルの使用 ● Using BULK INSERT and Non-XML Format File
 ● BULK INSERT と XML フォーマット ファイルの使用 ● Using BULK INSERT and XML Format File
 ● OPENROWSET(BULK...) と XML 以外のフォーマット ファイルの使用 ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● OPENROWSET(BULK...) と XML フォーマット ファイルの使用 ● Using OPENROWSET(BULK...) and XML Format File

注意

XML 以外のフォーマット ファイルまたは XML フォーマット ファイルを使用して、データ ファイルをテーブルに一括インポートできます。その場合、bcp ユーティリティ コマンド、BULK INSERT ステートメント、または INSERT ...SELECT * FROM OPENROWSET(BULK...) ステートメント。Either a non-XML or XML format file can be used to bulk import a data file into the table by using a bcp utility command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. 詳細については、「データの一括インポートでのフォーマット ファイルの使用 (SQL Server)」を参照してください。For more information, see Use a Format File to Bulk Import Data (SQL Server).

テスト条件の例Example Test Conditions

このトピックの変更するフォーマット ファイルの例は、以下に定義されたテーブルとデータ ファイルに基づいています。The examples of modified format files in this topic are based on the table and data file defined below.

サンプル テーブルSample Table

以下のスクリプトでは、テスト データベースと myRemapという名前のテーブルが作成されます。The script below creates a test database and a table named myRemap. Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

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

サンプル データ ファイルSample Data File

以下のデータは、 FirstNameLastName をテーブル myRemapと逆の順序で示しています。The data below presents FirstName and LastName in the reverse order as presented in the table myRemap. メモ帳を使用して、空のファイル D:\BCP\myRemap.bcp を作成し、次のデータを挿入します。Using Notepad, create an empty file D:\BCP\myRemap.bcp and insert the following data:

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

フォーマット ファイルの作成Creating the Format Files

myRemap.bcp から myRemap テーブルにデータを一括インポートするには、フォーマット ファイルで次の操作を行う必要があります。To bulk import data from myRemap.bcp into the myRemap table, the format file must do the following:

  • 最初のデータ フィールドを最初の列 PersonIDにマップします。Map the first data field to the first column, PersonID.
  • 2 番目のデータ フィールドを 3 番目の列 LastNameにマップします。Map the second data field to the third column, LastName.
  • 3 番目のデータ フィールドを 2 番目の列 FirstNameにマップします。Map the third data field to the second column, FirstName.
  • 4 番目のデータ フィールドを 4 番目の列 Genderにマップします。Map the fourth data field to the fourth column, Gender.

フォーマット ファイルを作成する最も簡単な方法は、 bcp ユーティリティを使用することです。The simplest method to create the format file is by using the bcp utility. 最初に、既存のテーブルからベース フォーマット ファイルを作成します。First, create a base format file from the existing table. 次に、実際のデータ ファイルを反映するようにベース フォーマット ファイルを変更します。Second, modify the base format file to reflect the actual data file.

XML 以外のフォーマット ファイルの作成Creating a Non-XML Format File

詳細については、「 XML 以外のフォーマット ファイル (SQL Server) 」を参照してください。Please review Non-XML Format Files (SQL Server) for detailed information. 次のコマンドでは、 bcp ユーティリティ を使用し、 myRemap.fmtのスキーマに基づいて XML 以外のフォーマット ファイル myRemapを生成します。The following command will use the bcp utility to generate a non-xml format file, myRemap.fmt, based on the schema of myRemap. さらに、修飾子 c を使用して文字データを指定し、 t, を使用してフィールド ターミネータとしてコンマを指定し、 T を使用して統合セキュリティによる信頼された接続を指定します。In addition, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

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

XML 以外のフォーマット ファイルの変更 Modifying the Non-XML Format File

用語については、「 XML 以外のフォーマット ファイルの構造 」を参照してください。See Structure of Non-XML Format Files for terminology. メモ帳で D:\BCP\myRemap.fmt を開き、次のように変更します。Open D:\BCP\myRemap.fmt in Notepad and perform the following modifications:

  1. 行が myRemap.bcpのデータと同じ順序になるようにフォーマット ファイルの行の順序を配置し直します。Re-arrange the order of the format-file rows so that the rows are in the same order as the data in myRemap.bcp.
  2. ホスト ファイル フィールドの順序の値が順番になっていることを確認します。Ensure the host file field order values are sequential.
  3. 最後のフォーマット ファイル行の後にキャリッジ リターンがあることを確認します。Ensure there is a carriage return after the last format-file row.

フィードバックをお待ちしております。 この記事の手順やコード例の中で、古い情報や間違っている情報を見つけた場合は、ぜひお知らせください。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. このページの下部にある [フィードバック] セクション内で [このページ] ボタンをクリックしてください。You can click the This page button in the Feedback section at the bottom of this page. SQL に関するフィードバックのすべての項目に目を通しています (通常は翌日)。We read every item of feedback about SQL, typically the next day. よろしくお願いいたします。Thanks.

変更内容を比較します。Compare the changes:
[指定日付より前]Before

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

AfterAfter

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

変更されたフォーマット ファイルは次のように反映されます。The modified format file now reflects:

  • myRemap.bcp の最初のデータ フィールドは最初の列にマップされます: myRemap.. PersonIDThe first data field in myRemap.bcp is mapped to the first column, myRemap.. PersonID
  • myRemap.bcp の 2 番目のデータ フィールドは 3 番目の列にマップされます: myRemap.. LastNameThe second data field in myRemap.bcp is mapped to the third column, myRemap.. LastName
  • myRemap.bcp の 3 番目のデータ フィールドは 2 番目の列にマップされます: myRemap.. FirstNameThe third data field in myRemap.bcp is mapped to the second column, myRemap.. FirstName
  • myRemap.bcp の 4 番目のデータ フィールドは 4 番目の列にマップされます: myRemap.. GenderThe fourth data field in myRemap.bcp is mapped to the fourth column, myRemap.. Gender

XML フォーマット ファイルの作成 Creating an XML Format File

詳細については、「 XML フォーマット ファイル (SQL Server) 」を参照してください。Please review XML Format Files (SQL Server) for detailed information. 次のコマンドでは、 bcp ユーティリティ を使用し、 myRemap.xmlのスキーマに基づいて XML フォーマット ファイル myRemapを生成します。The following command will use the bcp utility to create an xml format file, myRemap.xml, based on the schema of myRemap. さらに、修飾子 c を使用して文字データを指定し、 t, を使用してフィールド ターミネータとしてコンマを指定し、 T を使用して統合セキュリティによる信頼された接続を指定します。In addition, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. XML ベースのフォーマット ファイルを生成する場合は、 x 修飾子を使用する必要があります。The x qualifier must be used to generate an XML-based format file. コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

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

XML フォーマット ファイルの変更 Modifying the XML Format File

用語については、「 XML フォーマット ファイルのスキーマ構文 」を参照してください。See Schema Syntax for XML Format Files for terminology. メモ帳で D:\BCP\myRemap.xml を開き、次のように変更します。Open D:\BCP\myRemap.xml in Notepad and perform the following modifications:

  1. フォーマット ファイルで <FIELD> 要素が宣言される順序は、データ ファイルでフィールドが表示される順序と同じです。したがって、ID 属性 2 と 3 を使用して <FIELD> 要素の順序を逆にします。The order in which the <FIELD> elements are declared in the format file is the order in which those fields appear in the data file, thus reverse the order for the <FIELD> elements with ID attributes 2 and 3.
  2. <FIELD> ID 属性値が順番になっていることを確認します。Ensure the <FIELD> ID attribute values are sequential.
  3. <ROW> 要素内の <COLUMN> 要素の順序により、一括操作で返される順序が決定されます。The order of the <COLUMN> elements in the <ROW> element defines the order in which they are returned by the bulk operation. XML フォーマット ファイルでは、一括インポート操作の対象になるテーブルの列とのリレーションシップがない各 <COLUMN> 要素にローカル名が割り当てられます。The XML format file assigns each <COLUMN> element a local name that has no relationship to the column in the target table of a bulk import operation. <COLUMN> 要素の順序は、<RECORD> 定義の <FIELD> 要素の順序とは関係ありません。The order of the <COLUMN> elements is independent of the order of <FIELD> elements in a <RECORD> definition. 各 <COLUMN> 要素は、<FIELD> 要素に対応しています (FIELD> 要素の ID は、<COLUMN> 要素の SOURCE 属性で指定されます)。Each <COLUMN> element corresponds to a <FIELD> element (whose ID is specified in the SOURCE attribute of the <COLUMN> element). このため、<COLUMN> SOURCE の値は、リビジョンを必要とする属性のみとなります。Thus, the values for <COLUMN> SOURCE are the only attributes that require revision. 順序を反転 <COLUMN> SOURCE 属性 2 および 3 の順番を逆にします。Reverse the order for <COLUMN> SOURCE attributes 2 and 3.

変更内容を比較します。Compare the changes
[指定日付より前]Before

\<?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>

AfterAfter

\<?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>

変更されたフォーマット ファイルは次のように反映されます。The modified format file now reflects:

  • COLUMN 1 に対応する FIELD 1 は、最初のテーブル列にマップされます: myRemap.. PersonIDFIELD 1, which corresponds to COLUMN 1, is mapped to the first table column, myRemap.. PersonID
  • COLUMN 2 に対応する FIELD 2 は、3 番目のテーブル列に再マップされます: myRemap.. LastNameFIELD 2, which corresponds to COLUMN 2, is re-mapped to the third table column, myRemap.. LastName
  • COLUMN 3 に対応する FIELD 3 は、2 番目のテーブル列に再マップされます: myRemap.. FirstNameFIELD 3, which corresponds to COLUMN 3, is re-mapped to the second table column, myRemap.. FirstName
  • COLUMN 4 に対応する FIELD 4 は、4 番目のテーブル列にマップされます: myRemap.. GenderFIELD 4, which corresponds to COLUMN 4, is mapped to the fourth table column, myRemap.. Gender

フォーマット ファイルを使用してデータをインポートして、テーブル列をデータ ファイル フィールドにマッピングするImporting Data with a Format File to Map Table Columns to Data-File Field

次の例では、データベース、データ ファイル、および上記で作成したフォーマット ファイルを使用します。The examples below use the database, datafile, and format files created above.

bcpXML 以外のフォーマット ファイルの使用Using bcp and Non-XML Format File

コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

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

bcpXML フォーマット ファイルの使用Using bcp and XML Format File

コマンド プロンプトで、次のコマンドを入力します。At a command prompt, enter the following command:

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

BULK INSERTXML 以外のフォーマット ファイルの使用Using BULK INSERT and Non-XML Format File

Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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 フォーマット ファイルの使用Using BULK INSERT and XML Format File

Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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 以外のフォーマット ファイルの使用Using OPENROWSET(BULK...) and Non-XML Format File

Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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 フォーマット ファイルの使用Using OPENROWSET(BULK...) and XML Format File

Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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;

参照See Also

bcp ユーティリティ bcp Utility
フォーマット ファイルを使用したテーブル列のスキップ (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)Use a Format File to Skip a Data Field (SQL Server)