Table of Contents
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: