Oracle 11g: INSERT SELECT with WITH statement

Question

I've written this simple example DML with WITH statement:

WITH seq AS
 (SELECT to_char(LEVEL - 1) p FROM dual CONNECT BY LEVEL <= 10)
SELECT old_value,
       new_value
  FROM (SELECT to_char(t1.p) old_value,
               to_char(t2.p) new_value
          FROM (SELECT row_number() over(ORDER BY p) rn,
                       p
                  FROM seq) t1,
               (SELECT row_number() over(ORDER BY dbms_random.random) rn,
                       p
                  FROM seq) t2
         WHERE t1.rn = t2.rn);

Is there any way to use WITH statement in INSERT statement?

FIDDLE

Community · Accepted Answer · 2017-05-23 12:29:19Z

3

as mentioned here : https://forums.oracle.com/thread/696477 and here : insert statement with with clause invalid identifier oracle

the WITH clause is part of the select statemant , so just try to wrap it all with an INSERT statement as below :

INSERT INTO SOME_TABLE    
WITH seq AS
     (SELECT to_char(LEVEL - 1) p FROM dual CONNECT BY LEVEL <= 10)
    SELECT old_value,
           new_value
      FROM (SELECT to_char(t1.p) old_value,
                   to_char(t2.p) new_value
              FROM (SELECT row_number() over(ORDER BY p) rn,
                           p
                      FROM seq) t1,
                   (SELECT row_number() over(ORDER BY dbms_random.random) rn,
                           p
                      FROM seq) t2
             WHERE t1.rn = t2.rn);

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Jul 24, 2013 at 15:05

planben

7006 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kirill Leontev · Accepted Answer · 2013-07-24 15:02:00Z

1

Just put insert on the top.

18:04:23 HR@sandbox> create table t (k number, v number);

Table created.

Elapsed: 00:00:00.84
23:00:22 HR@sandbox> insert into t
23:00:34   2  with tt as (
23:00:39   3  select rownum, rownum*10 from dual
23:00:47   4  connect by rownum < 10
23:00:54   5  )
23:00:55   6  select * from tt
23:00:58   7  /

9 rows created.

Elapsed: 00:00:00.24

answered Jul 24, 2013 at 15:02

Kirill Leontev

11k8 gold badges46 silver badges50 bronze badges

Collectives™ on Stack Overflow

Oracle 11g: INSERT SELECT with WITH statement

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related