Table of Contents
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;
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;
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
HIRE_DATE
FROM EMP_UNCON_JOB;
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');
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IN (145, 146);
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;
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:
