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
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);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);
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');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;
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');
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);
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;
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;
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;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;
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'));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;
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;
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);
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);
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);
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);
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'));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;
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;
