SQL Server への CSV データのインポート

CSVファイルのデータをSQL Serverへ取り込むといった要望は結構あるのではないかと思います。
例えば商品データベースをバッチで更新するのにCSVファイルをアップロードするような場合です。

Integration Services を利用する。
bcp コマンドを利用する。
などの方法がありますが、BULK INSERT を用いた方法をメモしておきます。

例として以下のような商品テーブルを考えます。

[T_商品]

製品番号 nvarchar(50)
商品名 nvarchar(50)
価格 int

◆インポートしたいデータがカンマやタブで区切られている場合

[インポートデータ1.csv]

1234-5678,フォーク,120
9876-5432,スプーン,100

以下のような式でインポートすることができます。

BULK INSERT T_商品 FROM 'C:\インポートデータ1.csv'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);

データがタブ区切りの場合「FIELDTERMINATOR = '\t'」を指定します。


◆インポートしたいデータ内に区切り文字が含まれテキスト修飾子がある場合

[インポートデータ2.csv]

"1234-56789","おもちゃA 対象年齢2,3才",1200
"9876-54321","おもちゃB 付属品C,D",1000

このようなデータを前述のようにカンマ区切りで単純にインポートしたのでは
フィールドに含まれるカンマでも区切ってしまいエラーとなります。
この場合、フォーマットファイルというものを別途作成します。

[フォーマットファイル.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="&quot;"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="&quot;,"/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="2" NAME="number" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="3" NAME="name" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="4" NAME="price" xsi:type="SQLINT"/>
  </ROW>
</BCPFORMAT>

<RECORD>に各<FIELD>をどこで区切るかを記述します。
そして<ROW>にはどのデータ（ID）がインポート先のどのフィールドに合致するかを記述します。
ただし<COLUMN>のNAME属性の値はデータベースのフィールド名と一致しなくても構いません。


この場合に記述すべきSQL文は以下のとおりです。

BULK INSERT T_商品
FROM 'C:\インポートデータ2.csv'
WITH (FORMATFILE='C:\フォーマットファイル.xml');

TRUNCATE TABLE T_商品;

などと組み合わせてストアドプロシージャに記述し、ジョブに登録するなどして
定期的にデータのリフレッシュなどに利用できます。



[参考]
一括インポート操作と一括エクスポート操作について

BULK INSERT (Transact-SQL)

XML フォーマット ファイルのスキーマ構文