Insert into Select ...

Insert into Select

Insert Into Select:  This SQL statement is pretty straight forward that needs no explanation. However, I will explain it anyway, "Insert into select" statement selects data from one table and inserts into another.

--This copies data from local_table2 and inserts into local_table1.
INSERT INTO local_table1 
            (column1, 
             column2, 
             column3) 
SELECT column1, 
       column2, 
       column3 
FROM   local_table2 
WHERE  ROWNUM < 100;

--Another Quick way to Insert all records
INSERT INTO local_table2 
SELECT * 
FROM   local_table1; 

Similarly, you can copy data from remote database to local database using database link. DBA moves data from production to testing or development database environment using "insert into select" along with database link. If you are pulling data over 5 GB, you should consider data-pump utility over db link.  When you are running out of space, it is good to use database link.

INSERT INTO local_table 
            (column1, 
             column2, 
             column3) 
SELECT column1, 
       column2, 
       column3 
FROM   remote_table@prod_db_link 
WHERE  ROWNUM < 100;