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