Oracle SQL insert into with With clause

with clause in oracle
case in insert statement oracle
oracle insert multiple rows from select
create insert statement from select oracle
oracle select into table
pl/sql insert record into table
insert oracle techonthenet
oracle with function

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

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

WITH clause in insert statement?, Re: WITH clause in insert statement? Yes, but the WITH clause is part of the SELECT part of the insert: SQL> create table tab (x number, y number); The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table.

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

WITH Clause : Subquery Factoring in Oracle, WITH Clause : PL/SQL Declaration Section · WITH Clause Enhancements in '​dd-mm-rr')-51,1100,NULL,20); INSERT INTO EMP VALUES (7900,'JAMES'  The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query.

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

The Essential Guide to Oracle INSERT INTO SELECT Statement, This tutorial shows you how to use the Oracle INSERT INTO SELECT statement to insert data into a table from the result of a SELECT statement. 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!

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

WITH Clause inside PL-SQL, Can we use with clause in insert statement in Oracle? With Clause in Oracle is released in Oracle 9i release 2 to improve the performance of complex sql queries. The clause works like a global temporary tables of oracle which is used to improve the query speed of complex sql queries. This technique is also called as sub-query factoring as it is used to De-factor the subqueries.

How to use multiple With clauses in a view?, > create table tab (x number, y number); Table created. 2 rows created. The syntax for the Oracle INSERT statement when inserting a single record using the VALUES keyword is: INSERT INTO table (column1, column2, column_n ) VALUES (expression1, expression2, expression_n ); Or the syntax for the Oracle INSERT statement when inserting multiple records using a SELECT statement is:

SQL, Oracle SQL: Using subquery factoring in an INSERT statement. I would like to create rows for this table. Copy and paste the statement into SQL*Plus and execute. The conclusion can be: WITH and subquery factoring can in fact be used in INSERT statement, allowing even greater control over which rows will be generated and INSERT Statement. The INSERT statement adds one or more new rows of data to a database table. For a full description of the INSERT statement, see Oracle Database SQL Reference. Syntax. Description of the illustration insert_statement.gif. Keyword and Parameter Description. alias. Another (usually short) name for the referenced table or view.

Oracle SQL: Using subquery factoring in an INSERT statement , Syntax. The syntax for the Oracle INSERT statement when inserting a single record using the VALUES keyword is: INSERT INTO table (column1, column2,  To do it, you use the Oracle INSERT INTO SELECT statement as follows: INSERT INTO target_table (col1, col2, col3) SELECT col1, col2, col3 FROM source_table WHERE condition; The Oracle INSERT INTO SELECT statement requires the data type of the source and target tables match.

Comments
  • 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!
  • @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!)
  • In SQL Server 2008R2 you cannot use this syntax with mem tables (ie declare @t table (a int, b int, c int) )
  • 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!
  • @user: please post some sample data and expected output.