Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 9 July 2012

INSERT INTO MULTIPLE TABLE FROM A SINGLE QUERY

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
SELECTFROM 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

--Finally cleanup
DROP TABLE MAINTABLE;
DROP TABLE COUNTER1;
DROP TABLE COUNTER2;
DROP TABLE COUNTER3;
DROP TABLE COUNTER4;
DROP SEQUENCE MAINID_SEQUENCE;

0 comments:

Post a Comment