Insert Into Multiple Table From A Single Query in DBA

Simply creating one main table where initially data will store then simply create some test tables and a sequence for later use in this example.

CREATE TABLE MAINTABLE (MAINID NUMBER PRIMARY KEY,MAINDATA VARCHAR2(20));

desc  MAINTABLE;

CREATE TABLE COUNTER1 (COUNTER NUMBER PRIMARY KEY,MAINID NUMBER UNIQUE,MAINDATA VARCHAR2(20));

desc COUNTER1;

CREATE TABLE COUNTER2 (COUNTER NUMBER PRIMARY KEY,MAINID NUMBER UNIQUE,MAINDATA VARCHAR2(20));

desc COUNTER2;

CREATE TABLE COUNTER3 (COUNTER NUMBER PRIMARY KEY,MAINID NUMBER UNIQUE,MAINDATA VARCHAR2(20));

desc COUNTER3;

CREATE TABLE COUNTER4 (COUNTER NUMBER PRIMARY KEY,MAINID NUMBER UNIQUE,MAINDATA VARCHAR2(20));

desc COUNTER4;

CREATE SEQUENCE MAINID_SEQUENCE;

--Insert some data into the main table for use later
INSERT INTO MAINTABLE SELECT ROWNUM* -1, DBMS_RANDOM.STRING('A',20) FROM DUAL CONNECT BY LEVEL <=100;

COMMIT;
100 rows created.

 

SELECT * FROM MAINTABLE;

--Now actual insert with When and else clause in this I am using a sequence to satisfy the primary key of the COUNTERx table and then the two ‎column names from the select clause at the end.‎

INSERT ALL
 WHEN MAINID=-1 THEN INTO COUNTER1 VALUES (MAINID_SEQUENCE.NEXTVAL, MAINID,MAINDATA)

  WHEN MAINID=-10 THEN INTO COUNTER2 VALUES (MAINID_SEQUENCE.NEXTVAL, MAINID,MAINDATA)

  WHEN MAINID IN (-20,-30,-40,-50) THEN INTO COUNTER3 VALUES (MAINID_SEQUENCE.NEXTVAL, MAINID,MAINDATA)

  ELSE INTO COUNTER4 VALUES (MAINID_SEQUENCE.NEXTVAL, MAINID,MAINDATA)

  SELECT MAINID,MAINDATA FROM MAINTABLE ORDER BY MAINID DESC;

--Now check the results in different table what happened

SELECT *  FROM COUNTER1;
COUNTER MAINID MAINDATA
---------- ---------- ------------------------------
1          -1         KOUbduiAhICpdbxbuNzv
SELECT * FROM COUNTER2;
COUNTER MAINID MAINDATA
---------- ---------- ------------------------------
10        -10       iVnetpFQLJvmSBxIBBgq

SELECT * FROM COUNTER3;
COUNTER MAINID MAINDATA
---------- ---------- ------------------------------
20        -20       pHXBcWUjXlGOescLhfiC
30        -30       lvSVHGfQridbSJEllszq
40        -40       QWtSvjkTjxSlvBZQdIaF
50        -50       NaeupCIWHbjrRwizQwCI

SELECT * FROM COUNTER4;
COUNTER MAINID MAINDATA
---------- ---------- ------------------------------
2          -2         YuHfeHWZbtQFJvVIfLtB
3          -3         bMIByddwpmnguxPpWqAF
4          -4         oyGqEXxImrcVZHxWYkbU
5          -5         uJFWrfvflWNUKArfjFJk
6          -6         UFRPukCShrKQAGbgeCWp
7          -7         uLcxrUAJNGHtqtkouIpZ
8          -8         XGwlcpApCOUdRmhMaSZH
9          -9         rKLYkvFuOQFCITbWBcmu
11        -11       GFrLhwASOUHInMllzRpw
…..
……
99        -99       ZzZQmQaQxEAXbaJBCgCd
100      -100     GGbVtHZkXvxBYoXVXpVV