How to select from one table and insert into another?

1. Introduction

In this article, we’ll discuss on how to select from one table and insert into another table using Insert into statement and create table based on another table in oracle.

2. Using Insert into Statement (Copy data to existing table)

To insert data from another table using insert into statement, Select statement should be used directly after the column names of the normal insert query instead of using the Values keyword. The columns specified in the insert statement and select statement should be same and also in the same order.

we can select from one table and insert into another table by using the following sql query

INSERT INTO targettable
    (COLUMN_NAMES) //seperated by comma
    SELECT COLUMN_NAMES FROM sourcetable 

3. Create table based on records from another table

To create a new table based on the data available from another table, the following create table statement can be used. This will create table and also copy the data.

create table new_table as 
select * from old_table

To create a new table with only specific columns and its data, the following create table statement can be used.

create table new_table as 
select column1, column2 from old_table

To just create a empty table based on another table without copying the data, use a where clause which can never be true.

create table new_table as 
select * from old_table
where 1 = 2

Note: Create table as select only creates the table similar to the source table. It will not copy the any triggers, constraints or indexes from the source table. Those objects needs to be created manually, if required.

4. Conclusion

To summarize, we have discussed about how to select from one table and insert into another by using the Insert into statement and create table statements.

FAQs

How to select from one table and insert into another?

Insert into targettable
(COLUMN_NAMES) //seperated by comma
SELECT COLUMN_NAMES FROM sourcetable

How to create table based on another table from Oracle?

create table new_table as
select * from old_table