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

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

データ ファイルには、テーブルの列数よりも多くのフィールドを格納できます。 この記事では、テーブル列を対応するデータ フィールドにマッピングし、追加のフィールドを無視して、より多くのフィールドを含むデータ ファイルに対応するように XML 以外のフォーマット ファイルと XML 形式ファイルの両方を変更する方法について説明します。

詳細については、「 bcp を使用したフォーマット ファイルの作成 (SQL Server)」を参照してください。

テスト条件の例

この記事の変更されたフォーマット ファイルの例は、サンプル テーブルの myTestSkipField とデータ ファイルの D:\BCP\myTestSkipField.bcpに基づいています。 コード サンプルのローカル ファイルの場所を、コンピューター上のファイルの場所に変更します。

サンプル テーブル

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

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

サンプル データ ファイル

空のファイル D:\BCP\myTestSkipField.bcp を作成し、次のデータを挿入します。

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

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

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

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

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

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

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

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

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

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

1. FirstName のフォーマット ファイル行全体をコピーし、次の行の FirstName のすぐ後ろに貼り付けます。
2. 新しい行とすべての後続行のホスト ファイル フィールドの順序の値を 1 増やします。
3. データ ファイル内の実際のフィールド数が反映されるように、列数の値を増やします。
4. 2 番目のフォーマット ファイル行のサーバー列の順序を 2 から 0 に変更します。

加えた変更を比較します。

前

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

後

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

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

• データ フィールドが 4 つになります。
• myTestSkipField.bcp の最初のデータ フィールドは最初の列にマップされます: myTestSkipField.. PersonID
• myTestSkipField.bcp の 2 番目のデータ フィールドはどの列にもマップされません。
• myTestSkipField.bcp の 3 番目のデータ フィールドは 2 番目の列にマップされます: myTestSkipField.. FirstName
• myTestSkipField.bcp の 4 番目のデータ フィールドは 3 番目の列にマップされます: myTestSkipField.. LastName

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

詳細については、XML フォーマット ファイル (SQL Server) を参照してください。 次のコマンドでは、 bcp ユーティリティ を使用し、 myTestSkipField.xmlのスキーマに基づいて XML のフォーマット ファイル myTestSkipFieldを生成します。

• 修飾子 c を使用して文字データを指定します。
• t, は、フィールド ターミネータとしてコンマを指定するために使用されます
• T は、統合セキュリティを使用して信頼できる接続を指定するために使用されます。
• XML ベースのフォーマット ファイルを生成する場合は、 x 修飾子を使用する必要があります。

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

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

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

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

1. 2 番目のフィールド全体をコピーし、次の行の 2 番目のフィールドのすぐ後ろに貼り付けます。
2. 新しいFIELDと後続のFIELDごとに、FIELD IDの値を 1 ずつ増やします。
3. FirstNameのCOLUMN SOURCE値を 1 ずつ増やし、修正されたマッピングを反映するようにLastNameします。

加えた変更を比較します。

前

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

後

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

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

• データ フィールドが 4 つになります。
• FIELD COLUMN1に対応する1は、第1のテーブル列にマップされ、myTestSkipField.. PersonID
• FIELD 2 はどの COLUMN にも対応していないため、どのテーブル列にもマップされません。
• FIELD COLUMN3に対応する3は、第2のテーブル列にマップされ、myTestSkipField.. FirstName
• FIELD COLUMN4に対応する4は、第3のテーブル列にマップされ、myTestSkipField.. LastName

データ フィールドをスキップするためのフォーマット ファイルを使用してデータをインポートする

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

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

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

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

bcp および XML フォーマット ファイルを使用する (SQL Server)

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

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

BULK INSERT (Transact-SQL) および XML 以外のフォーマット ファイルを使用する

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

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 INSERT (Transact-SQL) および XML フォーマット ファイルを使用する (SQL Server)

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

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

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

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 (Transact-SQL) および XML フォーマット ファイルを使用する (SQL Server)

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

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;

関連コンテンツ

• bcp ユーティリティ
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• フォーマット ファイルを使用したテーブル列のスキップ (SQL Server)
• フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)