ぱそくま ろご
Windows > SQL Server > フォーマットファイルを使ったBULK INSERT

フォーマットファイルを使ったBULK INSERT

フォーマットファイルとは

通常のBULK INSERTでは取り込むデータファイルの列とテーブルの列定義が同じでなければなりません。 しかし、フォーマットファイルを使うことにより、列の並びや数が違ってもBULK INSERTが可能になります。 フォーマットファイルはXML形式とbcpユーティリティを利用した方法の2通りがありますが、 XML形式のほうが扱いやすい(すみません、主観が多少はいっているかもです。)ため、XML形式を中心に紹介します。

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

XML形式のフォーマットファイルは「<BCPFORMAT>」エレメントの中に、大きく別けて

  1. CSVファイルの列定義を行う「<RECORD>」エレメント
  2. インポート先のテーブルの列とのマッピング定義を行う「<ROW>」エレメント

から構成されます。「<BCPFORMAT>」は、特に注意する点はありません。おまじないのようにそのまま書けばOK。 「<RECORD>」CSVファイルの中身を単純につらつらと書き、最後の列定義だけTERMINATOR="\r\n"を宣言すればOK。 「<ROW>」は「<RECORD>」で宣言したIDと「<ROW>」のSOURCEとマッピングさせればOK。

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

例えば、以下のテーブルの以下のCSVファイルを

CSVの中身(c:\data.csv)
  • メールアドレス,名前(姓),名前(名),性別,電話番号
  • tarou@hoge.com,てすと,太郎,男,03-123-4567
  • hanako@hoge.com,てすと,花子,女0120-123-4567
  • .
  • .
挿入先のテーブル
  • CREATE TABLE dbo.MEMBER_INFO
  • (
  •  MAIL_ADDR VARCHAR(50) NOT NULL,
  •  FIRST_NAME VARCHAR(50) NOT NULL,
  •  LAST_NAME VARCHAR(50) NOT NULL,
  •  CONSTRAINT PK_MEMBER_INFO PRIMARY KEY CLUSTERED (MAIL_ADDR)
  • ) ON [PRIMARY]

以下の条件でインポートする場合、

  • メールアドレスはMAIL_ADDRにインポート。
  • 名前(姓)はLAST_NAMEにインポート。
  • 名前(名)はFIRST_NAMEにインポート。
  • 性別と電話番号はテーブルのフィールドにないのでインポートしない。


フォーマットファイルは以下のようになります。

blukinsertのフォーマットファイル(c:\format.xml)

 <?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=","/>
   <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=","/>
   <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=","/>
   <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=","/>
   <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n"/>
  </RECORD>

  <-- データファイルの列のINDEXを使って、インポート先を定義(マッピング) -->
  <ROW>
   <COLUMN SOURCE="1" NAME="MAIL_ADDR" xsi:type="SQLNVARCHAR"/>
   <COLUMN SOURCE="3" NAME="LAST_NAME" xsi:type="SQLNVARCHAR"/>
   <COLUMN SOURCE="2" NAME="FIRST_NAME" xsi:type="SQLNVARCHAR"/>
  </ROW>
 </BCPFORMAT>

「<RECORD>」はCSVファイルのフィールドをそのまま書きます。 サンプルのフィールドはメールアドレス、名前(姓)、名前(名)、性別、電話番号の5つですので、FIELDが1-5必要になります。 なお、最終列(サンプルだと電話番号)の終端は改行文字なので、TERMINATOR="\r\n"/を指定します。

次に「<ROW>」ですが、COLUMNの順番はテーブルの列フィールドの順番と同じです。 CSVファイルの列とテーブルのどの列を対応させるかは、 COLUMNのSOURCE属性に「<RECORD>」の「FIELD」のID属性を指定します。

BULK INSERT文は以下のようになります。

  • BULK INSERT pasodb.dbo.MEMBER_INFO
  • FROM 'D:\data\member.csv'
  • WITH
  • (
  • FIRSTROW = 2,
  • FIELDTERMINATOR = ',',
  • CHECK_CONSTRAINTS,
  • DATAFILETYPE='widechar'
  • FORMATFILE='c:\format.xml'
  • )

フォーマットファイルの属性一覧表

FIELD要素の属性
属性 必須 説明
ID="feild ID" ○ ID="1"等で指定。フィールドを識別できるIDを指定します。これは、COLUMN要素のSOURCE属性で参照できます。
xsi:type="fieled type" ○ 要素(データ)のタイプを指定します。 選択はNativeFixed,NativePrefix,CharFixed,NCharFixed,CharPrefix,NCharPrefix,CharTerm,NCharTermです。 CSVファイルのときは"CharTerm"でほぼ問題ありません。
COLUMN要素の属性
属性 必須 説明
SOURCE="feild ID" ○ 対象のFIELD要素のID属性を指定します。
NAME="column name" ○ テーブルの列名を指定します。厳密にいうと、結果セット内での列名として扱われるため、テーブルの列名でなくても可能です。もちろん、結果セット内での列名なので列名は一意になる必要はあります。
xsi:typecolumn type" -



COPYRIGHT © 2008 Deepnet Inc. ALL RIGHTS RESERVED.