CODEWITHSUNDEEP
sqloracle-databasedynamic-sql
johnny
johnny

Reputation:

SQL Command not properly ended

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.

Upvotes: 0

Views: 1608

Answers (3)

Rene
Rene

Reputation: 10541

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;

Upvotes: 3

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 1

Per Östlund
Per Östlund

Reputation: 1224

This maybe do what you want.

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

Upvotes: 0

Related Questions