insert into... select ... with subquery or without column order

Question

I'm using Oracle db and I wonder if it's possible to write something like:

INSERT INTO CL (select COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME='CL')
SELECT * FROM CLT;

or:

INSERT INTO CL (select COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME='CL')
SELECT (select COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME='CL') FROM CLT;

So the idea is that both tables have the same columns, but the columns order don't match, so when I try to do simple

INSERT INTO CL 
SELECT * FROM CLT;

I keep getting ORA-00932: inconsistent datatypes, it doesn't happen if I specify all columns one by one. But I don't want to do that, because my table has ~50 columns and I want to have robust solution that I could apply later to other tables as well.

That's why I was thinking about using subquery to get the column names in the INSERT INTO query but either this is not possible in sql or i'm doing something wrong.

Is there any way to skip order of the columns in that (and force sql to use the names maybe? ) or use subquery within that query to obtain two times all column names in the same order?

PS. I was thinking about reordering chaging them to INVISIBLE and back to VISIBLE but it's not supported in my version. Also it wouldn't be as reusable as I need.

Alex Poole Alex Poole · Accepted Answer · 2018-07-12T12:08:49

No, you can't use a subquery to generate the column list as part of an SQL statement.

You can generate the full statement from the data dictionary:

select 'insert into cl ("'
  || listagg(column_name, '","') within group (order by column_id)
  || '") select "'
  || listagg(column_name, '","') within group (order by column_id)
  || '" from clt'
from user_tab_columns where table_name = 'CLT';

and then either copy and paste that, or use dynamic SQL from an anonymous block:

declare
  stmt varchar2(4000);
begin
  select 'insert into cl ("'
    || listagg(column_name, '","') within group (order by column_id)
    || '") select "'
    || listagg(column_name, '","') within group (order by column_id)
    || '" from clt'
  into stmt
  from user_tab_columns where table_name = 'CLT';

  dbms_output.put_line(stmt); -- to check and debug
  execute immediate stmt;
end;
/

With a couple of dummy tables:

create table clt (col1 number, col2 date, col3 varchar2(10));
create table cl (col3 varchar2(10), col1 number, col2 date);

insert into clt (col1, col2, col3) values (42, date '2018-07-12', 'Test');

insert into cl
select * from clt;

SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

running that block gives:

insert into cl ("COL1","COL2","COL3") select "COL1","COL2","COL3" from clt

PL/SQL procedure successfully completed.

select * from cl;

COL3             COL1 COL2      
---------- ---------- ----------
Test               42 2018-07-12

You could also turn that anonymous block into a procedure that takes two table names if this is something you're likely to want to do often (you said it needed to be reusable, but that could mean for the same tables, and could just be a block in a script).

You could also go further and only include columns that appear in both tables, or verify data types match exactly; though that's a bit more work and may well not be necessary.

insert into... select ... with subquery or without column order

1 Answers