Insert within CTE
(1) By anonymous on 2021-10-05 20:48:44 [link] [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.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 [source]
You could use an insert together with RETURNING. Could you describe what you want to archive?