How to insert multiple rows into a table from values in same table or another

This can be done by

Single table

insert into TABLE1 (COL1,COL2,COL3)
select my_seq.nextval, a, 'Some other Value'
from
(SELECT COL2 as a FROM TABLE1 where COL3 ='some value' )

Multiple Table

insert into TABLE1 (COL1,COL2,COL3, COL4)
select my_seq.nextval, a, 'Some other Value',b
from
(SELECT COL2 as a FROM TABLE2 where COL3 ='some value'

UNION ALL
 SELECT COL4 as b FROM TABLE3 )