SELECTの結果から複数のデータを複数INSERTする

SQLで初期データを作成したいというのは、よくあるケースかなと思います。
例えば全ての企業(companies)に対して初期ユーザー(users)を1件登録する必要があるとします。企業とユーザーは1対多の関係にあり、以下のようにINSERTとSELECTを組み合わせることで簡単に全ての企業に対して初期ユーザーの追加が完了します。

企業一覧

postgres=# SELECT * FROM companies;
 id | name
----+------
  1 | A社
  2 | B社
  3 | C社
(3 rows)

各企業に初期ユーザーを追加

INSERT INTO users (name, company_id)
SELECT
  '初期ユーザー',
  companies.id
FROM
  companies
;

postgres=# SELECT * FROM users;
 id |     name     | company_id
----+--------------+------------
  1 | 初期ユーザー |          1
  2 | 初期ユーザー |          2
  3 | 初期ユーザー |          3
(3 rows)

ではSELECTの実行結果から複数のデータを複数INSERTするにはどうすれば良いでしょうか。
先ほどの例でいうと、初期ユーザとして「岡部・椎名・橋田」を登録するにはどのようなクエリを記述すれば良いでしょうか。自分が「SELECT INSERT 複数行」といったワードで調べた限り、この問題を解決する方法を紹介している方を発見することはできませんでした。

解決案: CROSS JOINを使う

試行錯誤の結果、CROSS JOINを使うことで上手くデータ追加ができました。
CROSS JOINの存在は認知していましたが、実際に使ったのは初めてだったので実績解除のような嬉しい気持ちがあります。INSERTと組み合わせるSELECTのFROMに対してCROSS JOINを指定することで、1つのレコードの対して複数のレコードを生成することができます。
また、登録したいレコード数の制御はUNION ALLを使うようにしています。

INSERT INTO users (name, company_id)
SELECT
    cross_table.name,
    companies.id
FROM
    companies
    CROSS JOIN (
        SELECT
            *
        FROM (
            SELECT '岡部' AS name UNION ALL
            SELECT '椎名' AS name UNION ALL
            SELECT '橋田' AS name
        ) AS subquery
    ) AS cross_table
ORDER BY
    companies.id
;

このクエリを実行すると各企業ごとに初期ユーザの「岡部・椎名・橋田」が追加されます。
ORDER BYでソートの指定を行ったのはIDを連番にさせたかったためです。

postgres=# SELECT * FROM users;
 id | name | company_id
----+------+------------
  4 | 岡部 |          1
  5 | 椎名 |          1
  6 | 橋田 |          1
  7 | 岡部 |          2
  8 | 椎名 |          2
  9 | 橋田 |          2
 10 | 岡部 |          3
 11 | 椎名 |          3
 12 | 橋田 |          3
(9 rows)

なぜCROSS JOINか

INSERTとSELECTを組み合わせることで複数のデータが登録がされるのはSELECTの実行結果が複数レコードであるためです。 例えばcompaniesからレコード一覧を取得すると1企業に対して1レコードが取得されます。つまりINSERTを実行する際にも、1レコード分のデータを追加することしかできません。
となるとSELECTの実行結果が複数行になるようにすれば良いということになります。
レコード数を一時的に増やすには結合を使えば良いですが、複数行のデータを任意の数だけ増やしたいとなればCROSS JOINを使って全組み合わせを作ってしまうのが一番楽だと判断しました。

気になるのはパフォーマンスです。
対象のテーブルのレコードとCROSS JOIN先のテーブルのレコード数が増えれば増えるほど、当然、パフォーマンスは悪化するので大量のデータを投入する必要がある場合は、注意が必要です。

SQLじゃなくても良い

今回は環境の制約で「実行できるのがSQLのみ」という指定があったため、頑張ってSQLを書きました。
ただ、SQL以外の選択肢を選択できるのであれば、無理に複雑なSQLを書く必要はありません。今回のような複雑な条件でのデータ作成はORMとプログラムを組み合わせた方がシンプルかつ早く実装することが可能でしょう。
自分も可能であればrails consoleを使いたかったです。ただ、制約があったのでやむなし...。

少しでも「ええな〜」と思ったらはてなスター・はてなブックマーク・シェアを頂けると励みになります。