Sayantan's Blog On Oracle

Insert operation in Oracle

Insert is a DML operation. Usually Insert means when we add rows into tables.

There are various ways rows can be added into tables.

Types of Insert operation:

• INSERT INTO VALUES CLAUSE
• COPYING DATA FROM OTHER TABLES
• SUBQUERY INSERT

INSERT INTO VALUES CLAUSE:

Creating setup:

CREATE TABLE EMP_DML
AS
SELECT * FROM EMP_SELECT
WHERE 1 = 2;

INSERT INTO EMP_DML 
(
 EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,
 JOB_ID,
 HIRE_DATE,
 SALARY,
 DEPARTMENT_ID
) 
VALUES (208,
        'Jhon',
        'Smith',
        'PU_CLERK',
        TO_DATE('07-DEC-2002','DD-MON-YYYY'),
        2000,
        30
       );

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_DML;

COPYING DATA FROM OTHER TABLES:

INSERT INTO EMP_DML 
(
 EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,
 JOB_ID,
 HIRE_DATE,
 SALARY,
 DEPARTMENT_ID
)
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM EMP_SELECT
WHERE EMPLOYEE_ID IN (115, 116);

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_DML;

SUBQUERY INSERT:

INSERT INTO (SELECT EMPLOYEE_ID,
                    FIRST_NAME,
                    LAST_NAME,
                    JOB_ID,
                    HIRE_DATE,
                    SALARY,
                    DEPARTMENT_ID
             FROM EMP_DML
             WHERE DEPARTMENT_ID = 30)
VALUES (
        117,
        'Sigal',
        'Tobias',
        'PU_CLERK',
        TO_DATE('24-JUL-2005','DD-MON-YYYY'),
        2800,
        30
       );

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       HIRE_DATE, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMP_DML;

RELATED TOPICS:

• MULTIPLE TABLE INSERT