René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Oracle SQL: Insert with Select and Subquery

set feedback off;
set pagesize 0;

create table foo (a number, b varchar(10), c varchar(10));

insert into foo values ( 15, 'abc','def' );
insert into foo values (998, 'max','min' );
insert into foo values (  7, 'bla','bla' );
insert into foo values (632, 'now','then');

insert into foo 
  (a,b,c)
  (select                   AA,
                            BB,
                            CC 
  from
    (select max(a) +1       AA      from foo), 
    (select 'new'           BB,
            'old'           CC      from dual));

select * from foo where a = (select max(a) from foo);

drop table foo;