Oracle SQL insert into with With clause

oracle, sql, with-statement

Solution

You may use as many 'helper_tables' as you wish.

create table t(helper1 varchar2(50) , helper2 varchar2(50) , dataElement varchar2(50) );


insert into t(helper1, helper2, dataelement)
with
     de as(select level lvl from dual connect by level <10)
     ,h1 as (select lvl, lvl/1.5 hp from de)
     ,h2 as (select lvl,  lvl/2 hp2 from de)
select h1.hp , h2.hp2, de.lvl
  from de 
        inner join
       h1 on de.lvl = h1.lvl
        inner join
       h2 on de.lvl = h2.lvl
/

With this in mind, you may be able to do all of your joins via normal joining of the tables to the master table

Problem

I'm new to sql, so maybe it is a dumb question, but is there any possibility to use With clause with Insert Into? Or are there any common workarounds? I mean something like this: ``` With helper_table As ( Select * From dummy2 ) Insert Into dummy1 Values (Select t.a From helper_table t Where t.a = 'X' ); ``` Thx! My example is too dummy, so I add some extended code (thx for the answers so far). ``` INSERT INTO dummy values (a,b) //more values WITH helper_table AS ( SELECT * FROM dummy2 ) WITH helper_table2 AS //from more tables ( SELECT * FROM dummy3 ) SELECT t.value as a, t2.value as b FROM helper_table t join helper_table t2 on t.value = t2.value //some join WHERE t.value = 'X' and t2.value = 'X' //other stuff ```

Original source