BULK INSERTしながらcsvデータを洗替したい

Question

SQLServer初心者です。よろしくお願いします。

環境
Microsoft Windows Server2012
Microsoft SQL Server 2012

目的：
    ＢＵＬＫＩＮＳＥＲＴでSQLserverにデータを取むタイミングで、CSVデータのあるカラムをコード変換してからテーブルにインサートしたい

質問：
BULKINSERTしながら、ロードされるCSVデータのあるカラムをコード変換したい方法を検討しています。コード変換の処理は、CSVデータのあるカラムの値を変換テーブルを参照し、変換後の値を取得するようにしようといますが、BULKINSERTでデータをインサートしながらCSVのデータ値を変換することが可能でしょうか？

Answer 1

BULK INSERTのページを確認してみましたが、残念ながらできそうもありません。

BULK INSERT (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms188365(v=sql.120).aspx

代替案としては、とりあえず洗い替え前のコードでBULK INSERTし、その後、SQL Sever上に変換テーブルを用意して、それを参照して一括で変換する。例えば、

update 変換対象テーブル set 変換対象テーブル.コード = 変換テーブル.変換後コード
from 変換対象テーブル
inner join 変換テーブル on 変換テーブル.変換前コード = 変換対象テーブル.コード

（参考）
SQL Serverで、SELECT結果でUPDATEする方法
http://sqlazure.jp/r/sql-server/403/

変換テーブルもBULK INSERTして用意すればいいですよね？

また、経験上、データ移行時に、旧コードと新コードの両方を１つのレコードに持つこともよく行います。ただ、もし、上記の方法でされるのであれば、変換テーブルが存在しますので、あまり必要ないかもしれません。

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 2

こんばんは、

BULK INSERT を使用する方法ではありませんが、OPENROWSET (BULK ...) 関数を使用する方法はいかがでしょうか。

docs.microsoft.com のドキュメント [1] では OPENROWSET 関数について次のように説明されています。

> OPENROWSET 関数は、テーブル名と同じように、クエリの FROM 句で参照できます。 OPENROWSET 関数は、INSERT、UPDATE、または DELETE ステートメントのターゲット テーブルとしても参照できます。

テキストファイルのフォーマットを定義したフォーマットファイルが別途必要にはなりますが、同ドキュメント「使用例」のセクションでは次のような SQL で タブ区切りや CSV のようなテキストファイルの内容を読み取ることができると説明されています。

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
    FORMATFILE = 'c:\test\values.fmt') AS a;

今回のご質問のケースでは「変換テーブルを参照し、変換後の値を取得する」とのことですから、上記使用例の SQL を変換テーブルと結合するように発展させ、INSERT ステートメントのターゲットとして参照することで、1 ステップの SQL でコード変換の条件を満たした INSERT ステートメントを実現できます。例えば、最終的にデータを挿入するテーブルを A, 変換テーブルを B と仮定した場合に次のような SQL で同様のことを実現できるかと思います（C と V はソーステキストのエイリアス）:

INSERT INTO A
  SELECT B.ConvertTo
    FROM B
    LEFT JOIN (
      SELECT * FROM OPENROWSET(
        BULK 'C:\Test\Values.csv',
        FORMATFILE = 'C:\Test\Values.fmt'
      ) AS C
    ) AS V ON B.ConvertFrom = V.Source;

回答中に言及したフォーマットファイルは bcp ユーティリティ で作成することができます。以下の参考 URL [2] でも、その作成手順が説明されています:

[1] OPENROWSET (Transact-SQL)
https://docs.microsoft.com/ja-jp/sql/t-sql/functions/openrowset-transact-sql

[2] フォーマット ファイルの作成 (SQL Server)
https://docs.microsoft.com/ja-jp/sql/relational-databases/import-export/create-a-format-file-sql-server

最後に、先に返信されている trapemiya さんの投稿は十分にご質問に回答されているように思いますので、参考になった投稿として投票させていただきます。もし、ご不明な点や気にかかることがまだあるようでしたらアップデートください。

---

--- Seiji Momoto