• [アーティクル]

データの一括インポートでのフォーマット ファイルの使用

このトピックでは、一括インポート操作でのフォーマット ファイルの使用方法について説明します。フォーマット ファイルでは、データ ファイルのフィールドがテーブルの列にマップされます。SQL Server 2005 以降のバージョンでは、XML 以外のフォーマット ファイルまたは XML フォーマット ファイルを使用して、データを一括インポートできます。この操作には、bcp コマンド、または Transact-SQL コマンドの BULK INSERT か INSERT ... SELECT * FROM OPENROWSET(BULK...) を使用します。

重要な注意事項重要

Unicode 文字データ ファイルを操作するフォーマット ファイルの場合、すべての入力フィールドが Unicode テキスト文字列 (つまり、固定サイズの Unicode 文字列または文字で終わる Unicode 文字列) でなければなりません。
注意注

フォーマット ファイルの詳細については、「XML 以外のフォーマット ファイルについて」および「XML フォーマット ファイルについて」を参照してください。

一括インポート コマンドのフォーマット ファイル オプション

次の表は、各一括インポート コマンドのフォーマット ファイル オプションを示しています。

一括読み込みコマンド

フォーマット ファイル オプションの使用方法

BULK INSERT

FORMATFILE = 'format_file_path'

INSERT ...SELECT * FROM OPENROWSET(BULK...)

FORMATFILE = 'format_file_path'

bcp ... in

-fformat_file

詳細については、「bcp ユーティリティ」、「BULK INSERT (Transact-SQL)」、または「OPENROWSET (Transact-SQL)」を参照してください。

注意注

SQLXML データを一括エクスポートまたは一括インポートする場合、フォーマット ファイルのデータ型には、SQLCHAR または SQLVARYCHAR (データはクライアント コード ページまたは照合順序で暗黙的に指定されるコード ページで送られます)、SQLNCHAR または SQLNVARCHAR (データは Unicode として送られます)、SQLBINARY または SQLVARYBIN (データは変換なしで送られます) のいずれかを使用します。

例

このセクションの例では、bcp コマンド、BULK INSERT ステートメント、および INSERT ...SELECT * FROM OPENROWSET(BULK...) ステートメント。一括インポートの例を実行する前に、サンプル テーブル、データ ファイル、およびフォーマット ファイルを作成する必要があります。

サンプル テーブル

次の例では、dbo スキーマに基づいて、myTestFormatFiles という名前のテーブルを AdventureWorks2008R2 サンプル データベースに作成する必要があります。このテーブルを作成するには、SQL Server Management Studio クエリ エディターで、次のコードを実行します。

USE AdventureWorks2008R2;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

USE AdventureWorks2008R2;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

サンプル データ ファイル

この例では、次のレコードが含まれているサンプル データ ファイル myTestFormatFiles-c.Dat を使用します。このデータ ファイルを作成するには、Microsoft Windows のコマンド プロンプトで次のように入力します。

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

サンプル フォーマット ファイル

このセクションの例では、XML フォーマット ファイル myTestFormatFiles-f-x-c.Xml を使用する場合と、XML 形式以外のフォーマット ファイルを使用する場合があります。どちらのフォーマット ファイルでも、文字データの形式と既定外のフィールド ターミネータ (,) を使用します。

XML 形式以外のフォーマット ファイルのサンプル

次の例では、bcp を使用して、myTestFormatFiles テーブルから XML フォーマット ファイルを生成します。myTestFormatFiles.Fmt ファイルには、次の情報が格納されます。

9.0
4
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     ","      2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     Col3         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS

9.0
4
1       SQLCHAR       0       7       ","      1     Col1         ""
2       SQLCHAR       0       100     ","      2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     Col3         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS

format オプションと共に bcp を使用して、このフォーマット ファイルを作成するには、Windows のコマンド プロンプトで次のように入力します。

bcp AdventureWorks2008R2..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

bcp AdventureWorks2008R2..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

フォーマット ファイルの作成方法の詳細については、「フォーマット ファイルの作成」を参照してください。

XML 形式のフォーマット ファイルのサンプル

次の例では、bcp を使用して、myTestFormatFiles テーブルから XML フォーマット ファイルを生成します。myTestFormatFiles.Xml ファイルには、次の情報が格納されます。

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

format オプションと共に bcp を使用して、このフォーマット ファイルを作成するには、Windows のコマンド プロンプトで次のように入力します。

bcp AdventureWorks2008R2..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

bcp AdventureWorks2008R2..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

bcp の使用

次の例では、bcp を使用して、myTestFormatFiles-c.Dat データ ファイルのデータを AdventureWorks2008R2 サンプル データベース内の HumanResources.myTestFormatFiles テーブルに一括インポートします。この例では、XML 形式のフォーマット ファイル MyTestFormatFiles.Xml を使用します。既存のテーブル行は、データ ファイルのインポート前に削除されます。

Windows のコマンド プロンプトで、次のように入力します。

bcp AdventureWorks2008R2..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T

bcp AdventureWorks2008R2..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T
注意注

このコマンドの詳細については、「bcp ユーティリティ」を参照してください。

BULK INSERT の使用

次の例では、BULK INSERT を使用して、myTestFormatFiles-c.Dat データ ファイルから AdventureWorks2008R2 サンプル データベース内の HumanResources.myTestFormatFiles テーブルにデータを一括インポートします。この例では、XML 形式以外のフォーマット ファイル MyTestFormatFiles.Fmt を使用します。既存のテーブル行は、データ ファイルのインポート前に削除されます。

SQL Server Management Studio クエリ エディターで、次のコードを実行します。

USE AdventureWorks2008R2;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO

USE AdventureWorks2008R2;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO
注意注

このステートメントの詳細については、「BULK INSERT (Transact-SQL)」を参照してください。

OPENROWSET 一括行セット プロバイダーの使用

次の例では、INSERT ... SELECT * FROM OPENROWSET(BULK...) を使用して、myTestFormatFiles-c.Dat データ ファイルのデータを AdventureWorks2008R2 サンプル データベース内の HumanResources.myTestFormatFiles テーブルに一括インポートします。この例では、XML 形式のフォーマット ファイル MyTestFormatFiles.Xml を使用します。既存のテーブル行は、データ ファイルのインポート前に削除されます。

SQL Server Management Studio クエリ エディターで、次のコードを実行します。

USE AdventureWorks2008R2;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO

USE AdventureWorks2008R2;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO

サンプル テーブルを使用し終わったら、次のステートメントを使用してテーブルを削除できます。

DROP TABLE myTestFormatFiles

DROP TABLE myTestFormatFiles
注意注

OPENROWSET BULK 句の詳細については、「OPENROWSET (Transact-SQL)」を参照してください。