Search code examples
sqloracle-databaseplsqlforallbulk-collect

Copy data from a table and load it into another table

I have a table 'A' having 40 columns. I need to copy the data from 20 specific columns from 'A' , to another table 'B' having those 20 columns. There will be around 3 - 10 million records. What will be the most efficient way to do this in PLSQL.

Solution

  • "daily table B will be truncated and new data will be inserted into it from A."

    Okay, so the most efficient way to do this is not to do it. Use a materialized view instead; a materialized view log on table A will allow you to capture incremental changes and apply them daily, or at any other window you like. Find out more.

    Compared to that approach using handrolled PL/SQL - or even pure SQL - is laughably inefficient.