[アーティクル]
10/25/2012

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

このトピックでは、一括インポート操作でのフォーマットファイルの使用方法について説明します。フォーマットファイルでは、データファイルのフィールドがテーブルの列にマップされます。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 (データは変換なしで送られます) のいずれかを使用します。

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)」を参照してください。

その他の例

フォーマットファイルの作成

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

フォーマットファイルを使用したテーブル列のスキップ

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

フォーマットファイルを使用したテーブル列とデータファイルフィールドのマッピング

次の方法で共有

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

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

例

サンプルテーブル

サンプルデータファイル

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

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

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

bcp の使用

BULK INSERT の使用

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

その他の例

関連項目

参照

概念

その他のリソース

次の方法で共有

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

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

例

サンプル テーブル

サンプル データ ファイル

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

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

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

bcp の使用

BULK INSERT の使用

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

その他の例

関連項目

参照

概念

その他のリソース

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

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

サンプルテーブル

サンプルデータファイル

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

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

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

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