Insert Into Select Statement in Oracle SQL | Oracle SQL Tutorials -8

I will explain Insert Into Select Statement in Oracle SQL in this post of Oracle SQL Tutorial series.

Insert Into Select Statement in Oracle SQL

Read the previous post of this tutorial series before this.

Insert Into in Oracle SQL | Oracle SQL Tutorials -7

INSERT INTO SELECT Statement

You can copy data from one table to another table using The INSERT INTO SELECT statement easily. But To use The INSERT INTO SELECT statement, source and target tables match.

When you use The INSERT INTO SELECT statement , existing records in the target table remain the same, and new records are appended the target table.

INSERT DATA FROM ANOTHER TABLE

• use sub-query to copy data from another table.
• The VALUES statement is not used.
• The column count in the subquery and INSERT statement must be same.
• All the records that came from the subquery is inserted to table.
• Let’s add a record to hr.jobs table from hr.departments.

INSERT INTO SELECT Syntax

The INSERT INTO SELECT statement syntax is as follows.

INSERT INTO table2 SELECT * FROM table1;

INSERT INTO table2 SELECT * FROM table1 WHERE condition;

INSERT INTO table2 (column1, column2, column3, ...)  SELECT column1, column2, column3, ... FROM table1;

INSERT INTO table2 (column1, column2, column3, ...)  SELECT column1, column2, column3, ... FROM table1 WHERE condition;

You can use the INSERT INTO SELECT statement as follows in the example.

INSERT INTO hr.jobs (job_id,
                     job_title,
                     min_salary,
                     max_salary)
  SELECT 'COMP_ENG',
          department_name,
          30000,
          80000
  FROM hr.departments
  WHERE department_id = 11;

COMMIT;

You can run the following insert into select statement, then query the table as follows.

SQL> INSERT INTO hr.jobs (job_id, job_title, min_salary, max_salary) SELECT 'COMP_ENG', department_name, 30000, 80000 FROM hr.departments WHERE department_id = 11;

1 row created.

SQL> commit;

Commit complete.

SQL> 

SQL> select * from hr.jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20080      40000
AD_VP      Administration Vice President            15000      30000
AD_ASST    Administration Assistant                  3000       6000
FI_MGR     Finance Manager                           8200      16000
FI_ACCOUNT Accountant                                4200       9000
AC_MGR     Accounting Manager                        8200      16000
AC_ACCOUNT Public Accountant                         4200       9000
SA_MAN     Sales Manager                            10000      20080
SA_REP     Sales Representative                      6000      12008
PU_MAN     Purchasing Manager                        8000      15000
PU_CLERK   Purchasing Clerk                          2500       5500

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN     Stock Manager                             5500       8500
ST_CLERK   Stock Clerk                               2008       5000
SH_CLERK   Shipping Clerk                            2500       5500
IT_PROG    Programmer                                4000      10000
MK_MAN     Marketing Manager                         9000      15000
MK_REP     Marketing Representative                  4000       9000
HR_REP     Human Resources Representative            4000       9000
PR_REP     Public Relations Representative           4500      10500
COMP_ENG   Computer Engineering                     30000      80000

20 rows selected.

SQL>

If all columns are matched, then no need to use the column name, you can use the Insert into select statement as follows.

insert into new_table select * from old_table;

Let’s make an real time example as follows.

SQL> 
SQL> create table hr.employees_new as select * from hr.employees where 1=0;

Table created.

SQL> select * from hr.employees_new;

no rows selected

SQL> 
SQL> 
SQL> insert into hr.employees_new select * from hr.employees;

107 rows created.

SQL> commit;

Commit complete.

SQL>

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )