SQLite Forum

Insert within CTE
Login

Insert within CTE

(1) By anonymous on 2021-10-05 20:48:44 [link]

Given this  CTE:

<code>
WITH RECURSIVE
cnt(x) AS (
 SELECT 1
 UNION ALL
 SELECT x+1 FROM cnt
  LIMIT 1000000
)
SELECT x FROM cnt;
</code>

Is it possible to add an <i>INSERT INTO</i>  statement within the CTE to write the values to table?

(2.1) By Keith Medcalf (kmedcalf) on 2021-10-05 21:33:32 edited from 2.0 in reply to 1

Do you mean something like:

```
create table t(x);
WITH RECURSIVE
cnt(x) AS (
 SELECT 1
 UNION ALL
 SELECT x+1 FROM cnt
  LIMIT 1000000
)
INSERT INTO t SELECT x FROM cnt;
```
?

Or perhaps,

```
create table x as
 WITH RECURSIVE cnt(x) 
   AS (
                 SELECT 1
       UNION ALL
                 SELECT x+1 
                   FROM cnt
           LIMIT 1000000
      )
      SELECT x FROM cnt;
```

(3) By Marco Bubke (marcob) on 2021-10-06 16:35:52 in reply to 1 [link]

You could use an insert together with RETURNING. Could you describe what you want to archive?