Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Insert operation in 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;
Insert operation in Oracle: Output

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;
Insert operation in Oracle : output

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:

Leave a Comment

Your email address will not be published.