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?