recent

Titulo

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; 

Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

No comments

Powered by Blogger.