INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;

Question

I would like to do an INSERT / SELECT, this means INSERT in the TARGET_TABLE the records of the SOURCE_TABLE, with this assumption:

The SOURCE and the TARGET table have only a SUBSET of common columns, this means in example:

==> The SOURCE TABLE has ALPHA, BETA and GAMMA columns;
==> The TARGET TABLE has BETA, GAMMA and DELTA columns.

What is the most efficient way to produce INSERT / SELECT statements, respecting the assumption that not all the target columns are present in the source table?

The idea is that the PL/SQL script CHECKS the columns in the source table and in the target table, makes the INTERSECTION, and then produces a dynamic SQL with the correct list of columns.

Please assume that the columns present in the target table, but not present in the source table, have to be left NULL.

I wish to extract the data from SOURCE into a set of INSERT statements for later insertion into the TARGET table.

You can assume that the TARGET table has more columns than the SOURCE table, anche that all the columns in the SOURCE table are present in the TARGET table in the same order.

Thank you in advance for your useful suggestions!

Answer 1

In Oracle, You can get common columns with this SQL query:

select column_name
  from user_tab_columns
 where table_name = 'TABLE_1'
intersect
select column_name
  from user_tab_columns
 where table_name = 'TABLE_2'

Then you iterate a cursor with the mentioned query to generate a comma separated list of all values returned. Put that comma separated string into a varchar2 variable named common_fields. Then, you can:

sql_sentence := 'insert into TABLE_1 (' || 
                 common_fields || 
                 ') select ' || 
                 common_fields ||
                 ' from TABLE_2';
execute immediate sql_sentence;