大量データ作成SQL

環境

1000万レコードをテーブルにinsertする。

create table ITEM_INFO
(NO varchar(10),
YM varchar(6),
ID varchar(8),
PRDCT varchar(30),
PRICE varchar(32),
OTHER1 varchar(30),
OTHER2 varchar(30),
OTHER3 varchar(30),
OTHER4 varchar(30),
OTHER5 varchar(30),
OTHER6 varchar(30),
OTHER7 varchar(30),
OTHER8 varchar(30),
OTHER9 varchar(30),
OTHER10 varchar(30),
OTHER11 varchar(30),
OTHER12 varchar(30),
OTHER13 varchar(30),
OTHER14 varchar(30),
OTHER15 varchar(30),
OTHER16 varchar(30),
OTHER17 varchar(30),
OTHER18 varchar(30),
OTHER19 varchar(30),
OTHER20 varchar(30),
OTHER21 varchar(30),
OTHER22 varchar(30),
OTHER23 varchar(30),
OTHER24 varchar(30),
OTHER25 varchar(30),
OTHER26 varchar(30),
OTHER27 varchar(30),
OTHER28 varchar(30),
OTHER29 varchar(30),
OTHER30 varchar(30),
OTHER31 varchar(30),
OTHER32 varchar(30),
OTHER33 varchar(30),
OTHER34 varchar(30),
OTHER35 varchar(30),
OTHER36 varchar(30),
OTHER37 varchar(30),
OTHER38 varchar(30),
OTHER39 varchar(30),
OTHER40 varchar(30),
OTHER41 varchar(30),
OTHER42 varchar(30),
OTHER43 varchar(30),
OTHER44 varchar(30),
OTHER45 varchar(30),
OTHER46 varchar(30),
OTHER47 varchar(30),
OTHER48 varchar(30),
OTHER49 varchar(30)
);

--実行時間計測
set timing on

--1000万件登録
insert into ITEM_INFO
--最初のselectにはinsertしたい項目を羅列する
select
    --1000万までの連番
    i + j
    
    --ランダム日付
    ,to_char(
        -- 2016/01から2018/09のランダム月を取得(YYYYMM形式)
        TO_DATE('20160101','YYYYMMDD') + MOD(ABS(DBMS_RANDOM.RANDOM()), TO_DATE('20181001', 'YYYYMMDD') - TO_DATE('20160101', 'YYYYMMDD')) , 'YYYYMM')

    --連番を8桁で右X埋め
    ,rpad(to_char(i + j),8,'X')

    --以降は固定値を入れている
    ,'りんご'
    ,'138'
    ,'OTHER1'
    ,'OTHER2'
    ,'OTHER3'
    ,'OTHER4'
    ,'OTHER5'
    ,'OTHER6'
    ,'OTHER7'
    ,'OTHER8'
    ,'OTHER9'
    ,'OTHER10'
    ,'OTHER11'
    ,'OTHER12'
    ,'OTHER13'
    ,'OTHER14'
    ,'OTHER15'
    ,'OTHER16'
    ,'OTHER17'
    ,'OTHER18'
    ,'OTHER19'
    ,'OTHER20'
    ,'OTHER21'
    ,'OTHER22'
    ,'OTHER23'
    ,'OTHER24'
    ,'OTHER25'
    ,'OTHER26'
    ,'OTHER27'
    ,'OTHER28'
    ,'OTHER29'
    ,'OTHER30'
    ,'OTHER31'
    ,'OTHER32'
    ,'OTHER33'
    ,'OTHER34'
    ,'OTHER35'
    ,'OTHER36'
    ,'OTHER37'
    ,'OTHER38'
    ,'OTHER39'
    ,'OTHER40'
    ,'OTHER41'
    ,'OTHER42'
    ,'OTHER43'
    ,'OTHER44'
    ,'OTHER45'
    ,'OTHER46'
    ,'OTHER47'
    ,'OTHER48'
    ,'OTHER49'
from
  (
    with data2(j) as
    (
      select 0 from dual
      union all
      select j+10000 from data2 where j < 9990000
    )
    select j from data2
  ),
  (
    with data1(i) as
    (
      select 1 from dual
      union all
      select i+1 from data1 where i < 10000
    )
    select i from data1
);
commit;

結果

10,000,000 rows inserted.
Elapsed: 00:03:27.323

3分程。