SQLite Forum

Insert within CTE
Login

Insert within CTE

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

Given this CTE:

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

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

(2) By Keith Medcalf (kmedcalf) on 2021-10-05 21:29:24 in reply to 1 updated by 2.1 [link] [source]

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;
```
?

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

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] [source]

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