フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)
適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
データ ファイルには、テーブルの列数よりも多くのフィールドを格納できます。 このトピックでは、XML 以外のフォーマット ファイルと XML フォーマット ファイルの両方を変更し、データ ファイルに多くのフィールドを格納する方法について説明します。この操作は、テーブル列を対応するデータ フィールドにマップし、余分なフィールドを無視することによって行います。 詳細については、「 フォーマット ファイルの作成 (SQL Server) 」を参照してください。
Note
XML 以外のフォーマット ファイルまたは XML フォーマット ファイルを使用して、データ ファイルをテーブルに一括インポートできます。その場合、 bcp ユーティリティ コマンド、 BULK INSERT ステートメント、または INSERT ...SELECT * FROM OPENROWSET(BULK...) ステートメント。 詳細については、「データの一括インポートでのフォーマット ファイルの使用 (SQL Server)」を参照してください。
Note
一括挿入を含むこの構文は、Azure Synapse Analytics ではサポートされていません。 Azure Synapse Analytics やその他のクラウド データベース プラットフォーム統合では、Azure Data Factory の COPY ステートメントを介して、または COPY INTO や PolyBase などの T-SQL ステートメントを使用してデータ移動を実行します。
テスト条件の例
このトピックの変更するフォーマット ファイルの例は、以下に定義されたテーブルとデータ ファイルに基づいています。
サンプル テーブル
以下のスクリプトでは、テスト データベースと 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
を開き、次のように変更します。
FirstName
のフォーマット ファイル行全体をコピーし、次の行のFirstName
のすぐ後ろに貼り付けます。- 新しい行とすべての後続行のホスト ファイル フィールドの順序の値を 1 増やします。
- データ ファイル内の実際のフィールド数が反映されるように、列数の値を増やします。
- 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
を開き、次のように変更します。
- 2 番目のフィールド全体をコピーし、次の行の 2 番目のフィールドのすぐ後ろに貼り付けます。
- 新しい FIELD と後続の各 FIELD の "FIELD ID" 値を 1 増やします。
FirstName
とLastName
の "COLUMN SOURCE" 値を 1 増やし、変更されたマッピングを反映するようにします。
加えた変更を比較します。
前
<?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 つになります。
- COLUMN 1 に対応する FIELD 1 は、最初のテーブル列にマップされます:
myTestSkipField.. PersonID
- FIELD 2 はどの COLUMN にも対応していないため、どのテーブル列にもマップされません。
- COLUMN 3 に対応する FIELD 3 は、2 番目のテーブル列にマップされます:
myTestSkipField.. FirstName
- COLUMN 4 に対応する FIELD 4 は、3 番目のテーブル列にマップされます:
myTestSkipField.. LastName
データ フィールドをスキップするためのフォーマット ファイルを使用してデータをインポートする
次の例では、データベース、データ ファイル、および上記で作成したフォーマット ファイルを使用します。
bcp と XML 以外のフォーマット ファイルを使用する
コマンド プロンプトで、次のコマンドを入力します。
bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T
bcp と XML フォーマット ファイルを使用する
コマンド プロンプトで、次のコマンドを入力します。
bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T
BULK INSERT と 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 と 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.xml');
GO
-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;
OPENROWSET(BULK...) と 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...) と 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.xml'
) AS t1;
GO
-- review results
SELECT * FROM TestDatabase.dbo.myTestSkipField;