20 Useful Oracle Insert Statement Examples

20 Useful Oracle Insert Statement Examples

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 5 mins read

In Oracle, INSERT INTO statement is used to insert a new record into a table. We can add the data in many ways in Oracle. In this post, I am giving 20 useful Oracle INSERT statement examples, which will be helpful for you.

Oracle Insert Statement Examples

  1. Simple Oracle Insert Statement Example

The following example inserts a record into EMP table.

Insert into SCOTT.EMP
   (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
 Values
   (7536, 'JOAN', 'CLERK', 7839, TO_DATE('20/07/2018 19:05:10', 'MM/DD/YYYY HH24:MI:SS'),
    3975, 50);

  1. Subquery Instead of Table Name Example

The following statement uses a subquery instead of the table name.

INSERT INTO (SELECT empno, ename, job, sal, comm, deptno FROM emp)   
VALUES (7090, 'SEBASTIN', 'CLERK', 2090, NULL, 40);

  1. Insert Select From Another Table Example

The following statement will insert records into Bonus table from the EMP table for clerks and salesman whose salary is higher than 1000.

INSERT INTO bonus
   SELECT empno, sal * 5 / 100
   FROM emp
   WHERE SAL > 1000 
   AND JOB IN ('CLERK', 'SALESMAN');

  1. Insert Multiple Rows Using ALL Example

The following example will insert multiple rows into multiple tables.

INSERT ALL
  INTO bonus (empno, bonusamt)
VALUES (7839, 890)
  INTO bonus (empno, bonusamt)
VALUES (8933, 190)
  INTO dept (deptno, dname, loc)
VALUES (55, 'FINANCE', 'LONDON')
   SELECT * FROM DUAL;

  1. Remote Database Insert Example

The following insert statement will insert a record into the DEPT table at the remote database using DBLink.

INSERT INTO dept@rmtdb (deptno, dname, loc)
   VALUES (65, 'TECHNOLOGY', 'DALLAS');

  1. Insert Sequence Value Example

The following statement inserts a new row containing the next value of the employee sequence into the EMP table.

INSERT INTO emp
   VALUES  (empseq.nextval, 'ADAM', 'SALESMAN',
            8102, SYSDATE, 3200, NULL, 40);

  1. Partition Table Insert Example

The following example adds rows from DAILY_SALES into partition JUL18 of the SALES_HISTORY table.

INSERT INTO sales_history PARTITION (jul18)
   SELECT * FROM daily_sales;

  1. Insert Using BIND Variable Example

The following example returns the values of the inserted rows into output bind variables BIND1 and BIND2.

INSERT INTO emp VALUES (empseq.nextval, 'LUCAS', 'MANAGER',
                        7892, SYSDATE, 1800, NULL, 30)
   RETURNING sal*12, job INTO :bind1, :bind2;

  1. Insert with Return Clause Example

The following example returns the reference value for the inserted row into bind array.

INSERT INTO emp (empno, ename, job)
   VALUES ('1092', 'PHILIPS', 'MANAGER')
   RETURNING REF(emp) INTO :1;

  1. LOB Insert Example

The following example copies LONG data to a LOB column in the following existing table.

INSERT INTO resume_lob (lob_file)
   SELECT TO_LOB(lob_data) FROM resume_table;

  1. Insert Using BFILENAME Function Example

The following example inserts a row into the emp_resume table by referencing external file my_resume.docx using BFILENAME function.

INSERT INTO emp_resume (empno, emp_res)
   VALUES (6738, BFILENAME ('MYDOC', 'my_resume.docx'));

  1. Insert using EXECUTE IMMEDIATE Example

The following statement inserts a record into the bonus table using EXECUTE IMMEDIATE statement.

BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO bonus (empno, bonusamt) VALUES (7830, 990)';
END;

  1. EXECUTE IMMEDIATE Insert Using Bind Variable Example

The following statement inserts a record into the bonus table using EXECUTE IMMEDIATE statement with BIND variables.

BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO bonus (empno, bonusamt) VALUES (:1, :2)'
    using 9839, 898;
END;

  1. FORALL Insert Statement Example

The following example describes how to use an INSERT statement with FORALL. Assuming TABEMP is a table type array.

FORALL l_cnt IN tabemp.FIRST .. tabemp.LAST
INSERT INTO emp (ename,
deptno,
mgr,
hiredate,
sal,
comm)
VALUES (tabemp (l_cnt).ename,
tabemp (l_cnt).deptno,
tabemp (l_cnt).mgr,
tabemp (l_cnt).hiredate,
tabemp (l_cnt).sal,
tabemp (l_cnt).comm);

  1. Insert Statement Example Using Merge Statement

The following example will insert a row into the EMP table when not matched with the EMP2 table data where the department is equal to 21.

MERGE INTO emp a
USING (SELECT *
FROM emp2
WHERE deptno = 21) b
ON (a.empno = b.empno AND a.deptno = 21)
WHEN NOT MATCHED
THEN
INSERT (a.empno, a.ename, a.deptno)
VALUES (2928, 'MERGETEST', 21);

  1. Insert Using DEFAULT Values Example

Using Insert to Use the Default Value for a given column in a Table. Assume bonus amount is having a default value set 1000 in bonus table and the following example it will use the default bonus amount for first the insert, and for the second insert, it will insert the bonus amount 590.

INSERT INTO BONUS (empno, bonusamt) VALUES (3877, DEFAULT);
INSERT INTO BONUS (empno, bonusamt) VALUES (3727, 590);

  1. Insert Using View Example

The following example will create a view on EMP table and then will insert a record into EMP table using VIEW.

CREATE VIEW emp_view
AS
   SELECT empno, ename, deptno FROM emp;
INSERT INTO emp_view
    VALUES (8939, 'VINCENT', 33);

  1. Insert Into Date Column Example

The following example inserts a record into the EMP table by converting a string to date data type.

INSERT INTO emp (empno, ename, hiredate)
    VALUES (3983, 'DAVID', TO_DATE ('21jun2018', 'ddmonyyyy'));

  1. Insert Using WHEN Clause

The following example will insert the record if the department is equal to 20 then into EMP table and if the department is equal to 21 then into the EMP2 table by selecting data from the EMP3 table using WHEN clause.

INSERT
WHEN deptno = 20
THEN   INTO emp (empno, ename, deptno) VALUES (empno, ename, deptno)
WHEN deptno = 21
THEN   INTO emp2 (empno, ename, deptno) VALUES (empno, ename, deptno)
   SELECT empno, ename, deptno FROM emp3;

  1. Insert Using Cursor Values Example

The following example will insert the records into the EMP3 table by fetching the data from the EMP table using Cursor.

DECLARE
CURSOR c_emp
IS
SELECT empno, ename, deptno FROM emp;
BEGIN
FOR c IN c_emp
LOOP
INSERT INTO emp (empno, ename, deptno)
VALUES (c.empno, c.ename, c.deptno);
END LOOP;
END;

Reference:

Oracle Insert Docs

See also: