35

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
Felix Kling
  • 795,719
  • 175
  • 1,089
  • 1,143
user2424380
  • 1,393
  • 3
  • 16
  • 29

4 Answers4

45

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

Harrison
  • 8,970
  • 1
  • 32
  • 28
  • 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! – Mike Williamson Jan 12 '12 at 06:52
  • @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!) – Harrison Jan 14 '12 at 14:21
  • In SQL Server 2008R2 you cannot use this syntax with mem tables (ie `declare @t table (a int, b int, c int)` ) – ohmusama Aug 14 '14 at 17:17
11
INSERT
INTO    dummy1
WITH    helper_table AS
        (
        SELECT  *
        FROM    dummy2
        )
SELECT  t.a
FROM    helper_table t
WHERE   t.a = 'X'
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 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! – user2424380 May 04 '11 at 15:01
  • @user: please post some sample data and expected output. – Quassnoi May 04 '11 at 15:03
9

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
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
-1

Keep in mind, using CTE's is intended for a small amount of data. Having thousands of rows in CTE's may cause performance degradation.

This is because all the helper table content is stored in PGA if not in the TEMP

user3807115
  • 11
  • 1