SQL Command not properly ended

Question

I am getting the sql command not properly ended when hiting this line below. I want to insert into table A with data from table B. Both of them have the same columns but the sequence might be different. TIA!

Insert into a (select column_name from user_tab_columns where table_name = 'B')
select * from b

I am using pl/sql developer.

Answer 1

This maybe do what you want.

INSERT INTO a (col1, col2)
SELECT col1, col2
FROM b

Answer 2

In a similar situation I created a view over the destination table that had the columns in the appropriate order.

For example

Table A has columns (A, B, C)
Table B has columns (B, C, A)

You create a view like

CREATE VIEW A_V AS SELECT B,C,A FROM A;

Then you can do an insert into a_v select * from b;

The advantage is that, even if columns are added to table a but not to table b then, as long as they are nullable or have a default, the insert via the view still works.

I created the CREATE VIEW scripts automatically, looking up against USER_TAB_COLUMNS for table B.

Answer 3

Use dynamic SQL in a plsql procedure. Loop through the column names in a cursor for loop and add them to a string. Then execute the query. See code below (not tested)

declare
   l_query varchar2(32767);
   l_columns varchar2(32767);

   cursor c is select column_name
    from user_tab_columns
    where table_name=&table_name;

begin
   for r in c loop
     l_columns := l_columns ||','||r.columns_name;
   end loop;
   -- remove first ','
   l_columns := substr(l_columns,2);

   l_query := 'insert into a ('||l_columns||') select '||l_columns||' from &table_name;

   execute immediate l_query;
end;