Oracle で SELECT INSERT 文を生成する

データをコピーする場合、CREATE TABLE テーブル名 AS SELECT ... の形式で書くとテーブルのコピーを作ることができる。当然、INSERT INTO テーブル名 SELECT ... の形式で書けばデータコピーできる……と思うのが実は大間違い。この構文では、テーブルのカラム順とSELECT句のカラム順が一致している必要がある。しかし、実運用上、環境によって実際のカラム順が異なるなどということは良く発生する。

通常の INSERT 句であれば礼儀としてカラム名を指定しても、SELECT INSERT の場合には、項目が並んでいるのでうっかりカラム名の指定を忘れてしまうことも多い。

カラム名の指定を忘れてしまうことは防げないが、Oracle11g R2 で新たに追加された LISTAGG 関数を使うと SELECT INSERT 文を容易に生成することができる。例えば、同じ項目名の別テーブルにデータをコピーする場合には次のようなSQLを組めば良い。

SELECT
    'INSERT INTO ' || TABLE_NAME || ' (' 
    || (LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID))
    || ') SELECT '
    || (LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID))
    || ' FROM 移送元テーブル;'
FROM
    USER_TAB_COLUMNS
WHERE
    TABLE_NAME = '移送先テーブル'
GROUP BY
    TABLE_NAME