oracle

マルチ・テーブル・インサートinsert allの動作確認

insert allは1回のSQL実行で「複数のテーブル」に対し、insert valuesをまとめて実行できて便利ですが、12cから機能追加となった自動採番機能を適用したテーブルに対しては注意が必要です。(実質使えないでしょう)

まずはinsert all の便利なところを確認します

一度に複数テーブルに対しinsertできる事も大きな特徴で便利なところですが以下例は1つのテーブルに対し複数レコードINSERTするサンプルになっています。

insert all 文
insert all
into wardcity(prefectures,ward) values  ('千葉県','千葉市中央区')
into wardcity(prefectures,ward) values  ('千葉県','千葉市稲毛区')
into wardcity(prefectures,ward) values  ('千葉県','千葉市緑区')
select 1 from dual;

一度に複数テーブルに対しinsertできるのは便利ですが、1つのテーブルに複数件数のinsertに特化するのであれば、MySQLやPostgreSQLではもっとシンプルに書くことができます。Oracleも採用してほしいです。

実行例
-- 検証用テーブルの作成
SQL> create table wardcity
  2  (
  3      prefectures varchar2(4 char),
  4      ward        varchar2(20 char)
  5  );

表が作成されました。


-- まとめてinsert values実行
SQL> insert all
  2  into wardcity(prefectures,ward) values  ('千葉県','千葉市中央区')
  3  into wardcity(prefectures,ward) values  ('千葉県','千葉市稲毛区')
  4  into wardcity(prefectures,ward) values  ('千葉県','千葉市緑区')
  5  -- into prefecture(rid,name) values  (11,'埼玉県') <-- 別テーブルも記載可能(今回はコメント化)
  6  select 1 from dual
  7  ;

3行が作成されました。


-- insertの確認
-- この通り1回のSQL発行で3件のinsert valuesができました。
SQL> select * from wardcity;

PREFECTU WARD
-------- ----------------------------------------
千葉県   千葉市中央区
千葉県   千葉市稲毛区
千葉県   千葉市緑区

SQL>

insert all の注意点

まずは自動採番機能を適用したテーブルの動作確認

-- 自動採番機能を適用しテーブルの再作成
SQL> drop table wardcity purge;

表が削除されました。

-- 自動採番機能を適用したsid カラムを追加します。
SQL> create table wardcity
  2  (
  3      sid         NUMBER GENERATED BY DEFAULT AS IDENTITY
  4                  INCREMENT BY 1 START WITH 1 CACHE 100,
  5      prefectures varchar2(4 char),
  6      ward        varchar2(20 char)
  7  );

表が作成されました。


-- 通常のinsert values句にて1件ずつINSERTします。
SQL> insert into wardcity(prefectures,ward) values  ('千葉県','千葉市中央区');

1行が作成されました。

SQL> insert into wardcity(prefectures,ward) values  ('千葉県','千葉市稲毛区');

1行が作成されました。

SQL> insert into wardcity(prefectures,ward) values  ('千葉県','千葉市緑区');

1行が作成されました。

-- insert文で指定しなかったsidカラムには自動で採番された値が入っています。
SQL> select * from wardcity;

       SID PREFECTU WARD
---------- -------- ----------------------------------------
         1 千葉県   千葉市中央区
         2 千葉県   千葉市稲毛区
         3 千葉県   千葉市緑区

SQL>

自動採番機能を適用したテーブルへinsert allの実行

-- さきほどINSERTした値をtruncateします。
SQL> truncate table wardcity;

表が切り捨てられました。

-- insert all 実行
SQL> insert all
  2  into wardcity(prefectures,ward) values  ('千葉県','千葉市中央区')
  3  into wardcity(prefectures,ward) values  ('千葉県','千葉市稲毛区')
  4  into wardcity(prefectures,ward) values  ('千葉県','千葉市緑区')
  5  select 1 from dual
  6  ;

3行が作成されました。

-- insertデータの確認
SQL> select * from wardcity;

       SID PREFECTU WARD
---------- -------- ----------------------------------------
         4 千葉県   千葉市中央区
         4 千葉県   千葉市稲毛区
         4 千葉県   千葉市緑区

SQL>
-- この通りsidへの採番が期待通りになっていません。
-- 通常このようなsidカラムはPKが適用されている事が多いとおもうので
-- insert all でPK違反が発生する事になり、実質使えないですね。
スポンサーリンク