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?