フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)Use a Format File to Skip a Data Field (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 more fields than the number of columns in the table. このトピックでは、XML 以外のフォーマット ファイルと XML フォーマット ファイルの両方を変更し、データ ファイルに多くのフィールドを格納する方法について説明します。この操作は、テーブル列を対応するデータ フィールドにマップし、余分なフィールドを無視することによって行います。This topic describes modifying both non-XML and XML format files to accommodate a data file with more fields by mapping the table columns to the corresponding data fields and ignoring the extra fields. 詳細については、「 フォーマット ファイルの作成 (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 a Non-XML Format File
 ● XML フォーマット ファイルの作成 ● Creating an XML Format File
 ● XML フォーマット ファイルの変更 ● Modifying an XML Format File
データ フィールドをスキップするためのフォーマット ファイルを使用したデータのインポートImporting Data with a Format File to skip a Data 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

以下のスクリプトでは、テスト データベースと myTestSkipFieldという名前のテーブルが作成されます。The script below creates a test database and a table named myTestSkipField. 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 myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

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

空のファイル D:\BCP\myTestSkipField.bcp を作成し、次のデータを挿入します。Create an empty file D:\BCP\myTestSkipField.bcp and insert the following data:

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

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

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

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

フォーマット ファイルを作成する最も簡単な方法は、 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 ユーティリティ を使用し、 myTestSkipField.fmtのスキーマに基づいて XML 以外のフォーマット ファイル myTestSkipFieldを生成します。The following command will use the bcp utility to generate a non-xml format file, myTestSkipField.fmt, based on the schema of myTestSkipField. さらに、修飾子 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

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

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

  1. FirstName のフォーマット ファイル行全体をコピーし、次の行の FirstName のすぐ後ろに貼り付けます。Copy the entire format-file row for FirstName and paste it directly after FirstName on the next line.
  2. 新しい行とすべての後続行のホスト ファイル フィールドの順序の値を 1 増やします。Increase the host file field order value by one for the new row and all subsequent rows.
  3. データ ファイル内の実際のフィールド数が反映されるように、列数の値を増やします。Increase the number of columns value to reflect the actual number of fields in the data file.
  4. 2 番目のフォーマット ファイル行のサーバー列の順序を 2 から 0 に変更します。Modify the server column order from 2 to 0 for the second format-file row.

加えた変更を比較します。Compare the changes made:
[指定日付より前]Before

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

AfterAfter

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

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

  • データ フィールドが 4 つになります。4 data fields
  • myTestSkipField.bcp の最初のデータ フィールドは最初の列にマップされます: myTestSkipField.. PersonIDThe first data field in myTestSkipField.bcp is mapped to the first column, myTestSkipField.. PersonID
  • myTestSkipField.bcp の 2 番目のデータ フィールドはどの列にもマップされません。The second data field in myTestSkipField.bcp is not mapped to any column.
  • myTestSkipField.bcp の 3 番目のデータ フィールドは 2 番目の列にマップされます: myTestSkipField.. FirstNameThe third data field in myTestSkipField.bcp is mapped to the second column, myTestSkipField.. FirstName
  • myTestSkipField.bcp の 4 番目のデータ フィールドは 3 番目の列にマップされます: myTestSkipField.. LastNameThe fourth data field in myTestSkipField.bcp is mapped to the third column, myTestSkipField.. LastName

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

詳細については、「 XML フォーマット ファイル (SQL Server) 」を参照してください。Please review XML Format Files (SQL Server) for detailed information. 次のコマンドでは、 bcp ユーティリティ を使用し、 myTestSkipField.xmlのスキーマに基づいて XML のフォーマット ファイル myTestSkipFieldを生成します。The following command will use the bcp utility to create an xml format file, myTestSkipField.xml, based on the schema of myTestSkipField. さらに、修飾子 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.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

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

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

  1. 2 番目のフィールド全体をコピーし、次の行の 2 番目のフィールドのすぐ後ろに貼り付けます。Copy the entire second field and paste it directly after the second field on the next line.
  2. 新しい FIELD と後続の各 FIELD の "FIELD ID" 値を 1 増やします。Increase the "FIELD ID" value by 1 for the new FIELD and for each subsequent FIELD.
  3. FirstNameLastName の "COLUMN SOURCE" 値を 1 増やし、変更されたマッピングを反映するようにします。Increase the "COLUMN SOURCE" value by 1 for FirstName, and LastName to reflect the revised mapping.

加えた変更を比較します。Compare the changes made:
[指定日付より前]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="\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>

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

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

  • データ フィールドが 4 つになります。4 data fields
  • COLUMN 1 に対応する FIELD 1 は、最初のテーブル列にマップされます: myTestSkipField.. PersonIDFIELD 1 which corresponds to COLUMN 1 is mapped to the first table column, myTestSkipField.. PersonID
  • FIELD 2 はどの COLUMN にも対応していないため、どのテーブル列にもマップされません。FIELD 2 does not correspond to any COLUMN and thus, is not mapped to any table column.
  • COLUMN 3 に対応する FIELD 3 は、2 番目のテーブル列にマップされます: myTestSkipField.. FirstNameFIELD 3 which corresponds to COLUMN 3 is mapped to the second table column, myTestSkipField.. FirstName
  • COLUMN 4 に対応する FIELD 4 は、3 番目のテーブル列にマップされます: myTestSkipField.. LastNameFIELD 4 which corresponds to COLUMN 4 is mapped to the third table column, myTestSkipField.. LastName

データ フィールドをスキップするためのフォーマット ファイルを使用したデータのインポートImporting Data with a Format File to skip a Data 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.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

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

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

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.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 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 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 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 以外のフォーマット ファイルの使用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 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 フォーマット ファイルの使用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 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;

参照See Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
フォーマット ファイルを使用したテーブル列のスキップ (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)