Rao_S
asked on
How to use the "using" clause with "insert"?
hi,
i want to insert rows from one table into another - with the using clause like so...
insert into table1 using select * from table2;
table1 has the same structure as table2 except one extra column in the end which is nulable, so the above insert will not work..
if the strucute is exactly same then i have no problem with the above insert statement.
is there a easy way to insert into table1 without listing all the columns?
i want to insert rows from one table into another - with the using clause like so...
insert into table1 using select * from table2;
table1 has the same structure as table2 except one extra column in the end which is nulable, so the above insert will not work..
if the strucute is exactly same then i have no problem with the above insert statement.
is there a easy way to insert into table1 without listing all the columns?
ASKER
i know, i can do it that way.. i was hoping there was one swell swoop of inserting data with just one statement...
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
insert into table1 select a.col1, a.col2 ...., null from table2 a;
btw:
will generate your base select statement. all you have is to add the missing null value for the "new" col of table1...
btw:
select 'a.' || to_lower(a.column_name) || ','
from user_tab_columns a
where a.table_name = 'table2'
order by a.column_id;will generate your base select statement. all you have is to add the missing null value for the "new" col of table1...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you flow01! it worked!
elegant...like it flow01
nice points distribution...
ASKER
hi Alex140181, i wanted to distribute the point to all answers, but i remember, that once i was told to award points to the correct and best answer only...
Points should go the the correct answer and assist could go to answers that helps clarify something or provides part of the answer. In this case Flow01 have provided the easiest and most elegant solution. So i agree with the point distribution (and learned a bit on the way) ;-)
create a staging table temp_t2 as a select * from t2 where whatever...
Then alter the table temp_t2 adding the extra column from t1;
Then insert from temp_t2 into t1.
And finally drop table temp_t2