SQLでテーブルデータの一括作成、複製
多人数で同一環境を利用する、または負荷テストで大量のデータが必要となる
場合などに知っておくと便利なSQLテクニックの紹介になります。
[EMPテーブル]
ID: 整数型の列
NM: 文字列型の列
上記のようなテーブルに数千件、数万件規模のデータを登録するのは非常に面倒です。
以下のようなSQLを実行すれば1回でまとめて登録することが出来ます。
必要に応じて、接頭辞などを設定すれば規則性を持ったレコードも作成できます。
以下の例ではID列に10001~20000の範囲の値を持つレコードを作成しています。
なお、FROM句に指定するテーブルは任意で件数さえ条件に合えば何でも構いません。
・EMPテーブルに1万件のデータを一括作成するSQLの例
[Oracle] INSERT INTO EMP (ID, NM) SELECT ROWNUM + 10000, /* 他のデータと被らないように値を調整する */ 'HOGE' || trim(to_char(ROWNUM,'00000')) FROM EMP /* 作成したい件数以上のレコードを保持する任意のテーブル */ WHERE ROWNUM <= 10000 /* 作成したい件数を設定する */ [MySQL] INSERT INTO EMP (ID, NM) SELECT @rownum := @rownum + 1, concat('HOGE', lpad(@rownum - 10000, 5, '0')) FROM EMP, /* 作成したい件数以上のレコードを保持する任意のテーブル */ (select @rownum := 10000) as INIT /* 変数初期化 */ LIMIT 10000; /* 作成したい件数を設定する */
次はレコードの複製です。
以下のようなSALARYテーブルに上記で作成したEMPテーブルのレコードと
対応するレコードを複製します。
[SALARYテーブル]
ID: 整数型の列(EMPテーブルのIDと対応)
SALARY: 整数型の列
・SALARYテーブルにレコードを複製するSQLの例
INSERT INTO SALARY (ID, SALARY) SELECT EP.ID, SL.SALARY FROM SALARY SL, EMP EP WHERE SL.ID = 1 /* 複製元のレコードを特定する条件を指定する */ AND EP.ID BETWEEN 10001 AND 20000; /* 複製対象の条件を指定 */