Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Multiple Table Insert in Oracle

Multiple Table Insert in Oracle

INSERT INTO..SELECT statement can be used to insert data into multiple target tables based on certain conditions.

Types of Multiple Table Insert:

  • UNCONDITIONAL ALL INSERT
  • CONDITIONAL ALL INSERT
  • CONDITIONAL FIRST INSERT
  • PIVOT INSERT

UNCONDITIONAL ALL INSERT:

Setup creation:

SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       SALARY,
       JOB_ID,
       HIRE_DATE
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;
Multiple Table Insert in Oracle : output
Multiple Table Insert in Oracle : output
CREATE TABLE EMP_UNCON_SAL
AS
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       SALARY
FROM EMP_SELECT
WHERE 1 = 2;
CREATE TABLE EMP_UNCON_JOB
AS
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE
FROM EMP_SELECT
WHERE 1 = 2;
INSERT ALL
INTO EMP_UNCON_SAL VALUES (EMPLOYEE_ID, 
                           FIRST_NAME,
                           LAST_NAME,
                           SALARY
                          )
INTO EMP_UNCON_JOB VALUES (EMPLOYEE_ID, 
                           FIRST_NAME,
                           LAST_NAME,
                           JOB_ID,
                           HIRE_DATE
                          )
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       SALARY,
       JOB_ID,
       HIRE_DATE
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       SALARY 
FROM EMP_UNCON_SAL;
Multiple Table Insert in Oracle : output
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE
FROM EMP_UNCON_JOB;
Multiple Table Insert in Oracle : output

CONDITIONAL ALL INSERT:

Setup creation:

CREATE TABLE EMP_CON_MGR
AS
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       SALARY
FROM EMPLOYEES
WHERE 1 = 2;
CREATE TABLE EMP_CON_EMP
AS
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       SALARY,
       MANAGER_ID
FROM EMPLOYEES
WHERE 1 = 2;
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       MANAGER_ID
FROM EMPLOYEES
WHERE JOB_ID IN ('SA_MAN');
Multiple Table Insert in Oracle : output
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IN (145, 146);
Multiple Table Insert in Oracle : output
INSERT ALL
WHEN JOB_ID = 'SA_MAN' THEN
INTO EMP_CON_MGR VALUES (EMPLOYEE_ID, 
                         FIRST_NAME,
                         LAST_NAME,
                         JOB_ID,
                         SALARY
                        )
WHEN MANAGER_ID IN (145, 146) THEN                        
INTO EMP_CON_EMP VALUES (EMPLOYEE_ID, 
                         FIRST_NAME,
                         LAST_NAME,
                         JOB_ID,
                         SALARY,
                         MANAGER_ID
                        )
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       MANAGER_ID
FROM EMPLOYEES
WHERE JOB_ID IN ('SA_MAN')
OR MANAGER_ID IN (145, 146);
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,
       JOB_ID,
       SALARY 
FROM EMP_CON_MGR;
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       SALARY,
       MANAGER_ID
FROM EMP_CON_EMP;
Multiple Table Insert in Oracle : output

CONDITIONAL FIRST INSERT:

Setup creation:

CREATE TABLE EMP_CON_FIRST
AS
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       SALARY
FROM EMPLOYEES
WHERE 1 = 2;
CREATE TABLE EMP_CON_AC_ACCOUNT
AS
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       SALARY
FROM EMPLOYEES
WHERE 1 = 2;
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,
       JOB_ID,
       SALARY 
FROM EMPLOYEES
ORDER BY SALARY DESC
FETCH FIRST 3 ROWS ONLY;
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       MANAGER_ID
FROM EMPLOYEES
WHERE JOB_ID IN ('AC_ACCOUNT');
INSERT FIRST
WHEN SALARY >= 17000 THEN
INTO EMP_CON_FIRST VALUES (EMPLOYEE_ID, 
                           FIRST_NAME,
                           LAST_NAME,
                           JOB_ID,
                           SALARY
                        )
WHEN JOB_ID = 'SA_MAN' THEN
INTO EMP_CON_MGR VALUES (EMPLOYEE_ID, 
                         FIRST_NAME,
                         LAST_NAME,
                         JOB_ID,
                         SALARY
                        )                       
WHEN MANAGER_ID IN (145, 146) THEN 
INTO EMP_CON_EMP VALUES (EMPLOYEE_ID, 
                              FIRST_NAME,
                              LAST_NAME,
                              JOB_ID,
                              SALARY,
                              MANAGER_ID
                             )   
ELSE INTO EMP_CON_AC_ACCOUNT VALUES (EMPLOYEE_ID, 
                                     FIRST_NAME,
                                     LAST_NAME,
                                     JOB_ID,
                                     SALARY
                                    )                              
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       MANAGER_ID
FROM EMPLOYEES
WHERE SALARY >= 17000
OR JOB_ID IN ('SA_MAN', 'AC_ACCOUNT')
OR MANAGER_ID IN (145, 146);
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,
       JOB_ID,
       SALARY 
FROM EMP_CON_FIRST;
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,
       JOB_ID,
       SALARY 
FROM EMP_CON_AC_ACCOUNT;
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME,
       JOB_ID,
       SALARY 
FROM EMP_CON_MGR;
SELECT EMPLOYEE_ID, 
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       SALARY,
       MANAGER_ID
FROM EMP_CON_EMP;

PIVOT INSERT:

Setup creation:

CREATE TABLE DEPT_EMP_INFO_SOURCE
(DEPARTMENT_ID NUMBER,
 YEAR_2001     NUMBER,
 YEAR_2002     NUMBER,
 YEAR_2003     NUMBER,
 YEAR_2004     NUMBER,
 YEAR_2005     NUMBER,
 YEAR_2006     NUMBER,
 YEAR_2007     NUMBER,
 YEAR_2008     NUMBER
);
SELECT DEPARTMENT_ID, EXTRACT (YEAR FROM HIRE_DATE) YEAR, COUNT(EMPLOYEE_ID) COUNT_EMP
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (30, 50)
GROUP BY DEPARTMENT_ID, EXTRACT (YEAR FROM HIRE_DATE)
ORDER BY DEPARTMENT_ID, EXTRACT (YEAR FROM HIRE_DATE);
INSERT INTO DEPT_EMP_INFO_SOURCE 
(DEPARTMENT_ID,
 YEAR_2001,
 YEAR_2002,
 YEAR_2003,
 YEAR_2004,
 YEAR_2005,
 YEAR_2006,
 YEAR_2007,
 YEAR_2008
) 
VALUES (30,
        0,
        1,
        2,
        0,
        2,
        1,
        1,
        0
       );
INSERT INTO DEPT_EMP_INFO_SOURCE 
(DEPARTMENT_ID,
 YEAR_2001,
 YEAR_2002,
 YEAR_2003,
 YEAR_2004,
 YEAR_2005,
 YEAR_2006,
 YEAR_2007,
 YEAR_2008
) 
VALUES (50,
        0,
        0,
        3,
        4,
        12,
        13,
        9,
        4
       );
SELECT DEPARTMENT_ID, 
       YEAR_2001, 
       YEAR_2002, 
       YEAR_2003, 
       YEAR_2004, 
       YEAR_2005, 
       YEAR_2006, 
       YEAR_2007, 
       YEAR_2008 
FROM DEPT_EMP_INFO_SOURCE
ORDER BY DEPARTMENT_ID;
INSERT ALL
INTO DEPT_EMP_INFO_DTL VALUES(DEPARTMENT_ID, 2001, YEAR_2001)
INTO DEPT_EMP_INFO_DTL VALUES(DEPARTMENT_ID, 2002, YEAR_2002)
INTO DEPT_EMP_INFO_DTL VALUES(DEPARTMENT_ID, 2003, YEAR_2003)
INTO DEPT_EMP_INFO_DTL VALUES(DEPARTMENT_ID, 2004, YEAR_2004)
INTO DEPT_EMP_INFO_DTL VALUES(DEPARTMENT_ID, 2005, YEAR_2005)
INTO DEPT_EMP_INFO_DTL VALUES(DEPARTMENT_ID, 2006, YEAR_2006)
INTO DEPT_EMP_INFO_DTL VALUES(DEPARTMENT_ID, 2007, YEAR_2007)
INTO DEPT_EMP_INFO_DTL VALUES(DEPARTMENT_ID, 2008, YEAR_2008)
SELECT DEPARTMENT_ID, 
       YEAR_2001, 
       YEAR_2002, 
       YEAR_2003, 
       YEAR_2004, 
       YEAR_2005, 
       YEAR_2006, 
       YEAR_2007, 
       YEAR_2008 
FROM DEPT_EMP_INFO_SOURCE
ORDER BY DEPARTMENT_ID;
SELECT DEPARTMENT_ID, 
       YEAR_ID, 
       EMP_COUNT 
FROM DEPT_EMP_INFO_DTL
ORDER BY DEPARTMENT_ID, YEAR_ID;

RELATED TOPICS:

Leave a Comment

Your email address will not be published.