How to use the "using" clause with "insert"?

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RindbaekSenior ConsultantCommented:
You can do it this 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
Rao_SAuthor Commented:
i know, i can do it that way.. i was hoping there was one swell swoop of inserting data with just one statement...
Alexander Eßer [Alex140181]Software DeveloperCommented:
insert into table1 select a.col1, a.col2 ...., null from table2 a;

select 'a.' || to_lower(a.column_name) || ','
from user_tab_columns a
where a.table_name = 'table2'
order by a.column_id;

Open in new window

will generate your base select statement. all you have is to add the missing null value for the "new" col of table1...
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

if the extra column is the last column in the table1
insert into table1 select t2.*,   null  from table2 t2;
If the extra column is a date or number use
to_number(null) or to_date(null)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rao_SAuthor Commented:
thank you flow01! it worked!
RindbaekSenior ConsultantCommented: it flow01
Alexander Eßer [Alex140181]Software DeveloperCommented:
nice points distribution...
Rao_SAuthor Commented:
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...
RindbaekSenior ConsultantCommented:
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) ;-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.