Oracle SQL insert into with With clause

Question

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

Solution 1

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

Solution 2

INSERT
INTO    dummy1
WITH    helper_table AS
        (
        SELECT  *
        FROM    dummy2
        )
SELECT  t.a
FROM    helper_table t
WHERE   t.a = 'X'

Solution 3

You can do something like

INSERT INTO dummy1
  WITH helper_table AS (
    SELECT *
      FROM dummy2
    )
  SELECT t.a
    FROM helper_table t
   WHERE t.a = 'X';

For your updated query

INSERT
INTO    dummy values (a,b)  //more values
WITH    helper_table AS
    (
    SELECT  *
    FROM    dummy2
    ),
        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

Comments

[user2424380]

I think my example was too dummy. I have many values, and I would like to use a different helper table to each value. Is there any souliton for that? Thx and sorry!

[Quassnoi]

@user: please post some sample data and expected output.

[Mike Williamson]

Can this same sort of thing be done with Temporary Tables? E.g., instead of "create table t...", using "create table #t...". I have a fairly complex query I need to make, part of which needs to remain beyond just the immediate query, which is why I need the temporary table, but I don't want to write anything permanent. Thanks!

[Harrison]

@MikeWilliamson since the #t (temp table) as you have described it is a SQL Server temp table, this is an oracle table. I can attest that the above statement would work in an oracle global temp table, but this is a different beast than the SQL Server Temp table. I can only recommend you try it out, if the above syntax works in SQL Server for a normal table insert, I would expect it to work the same (note "expect" does not mean it will, nor have I tried it!)

[ohmusama]

In SQL Server 2008R2 you cannot use this syntax with mem tables (ie declare @t table (a int, b int, c int) )