Insert  Data Using SQL Query In Oracle Table

Topic Introduction: This tutorial will show how to Insert data using SQL query in Oracle Table. Here we will describe various structures and examples of various purposes.

Structure#1:   

INSERT INTO tab2

    SELECT *

      FROM tab1

     WHERE condition;

Example#1:   

INSERT INTO employees_it

    SELECT *

      FROM employees

     WHERE department_id = 60;

Using the above structure or example we can insert all columns from the employees' table to the employees_it table. Here we have used a where condition to restrict data from the employees' table.

Structure#2:   

INSERT INTO tab2 (col1,

                  col2,

                  col3,

                  col4,

                  col5)

    SELECT col1,

           col2,

           col3,

           col4,

           col5

      FROM tab1

     WHERE condition;

Example#2:    

INSERT INTO employees_it (empid,

                          lname,

                          phoneno,

                          jdate,

                          salary)

    SELECT employee_id,

           last_name,

           phone_number,

           hire_date,

           salary + salary * NVL (commission_pct, 0) / 100

      FROM employees

     WHERE department_id = 60;

Using the above structure or example we can insert selected columns from the employees' table to the employees_it table. Here we have used a where condition to restrict data from the employees' table.

Structure#3:   

INSERT INTO tab2 (col1, col2_val_from_other_table)

     VALUES ('val1',

             (SELECT val

                FROM tab2

               WHERE condition));

Example#3:    

INSERT INTO employees_it (lname, sal)

     VALUES ('name',

             (SELECT MAX(salary + salary * NVL (commission_pct, 0) / 100)

                FROM employees

               WHERE department_id = 60));

Using the above structure or example we can insert value static and selected single column value from the employees' table to the employees_it table. Here we have used a where condition to restrict data from the employees' table.