[sqlite] Using CTE with INSERT

[Clemens Ladisch]

Don V Nielsen wrote:
> I'm struggling to implement an INSERT statement that get's is values from a
> CTE.
>
> insert into vo_crrt_pieces (recid)
> values (
>   with
>   pkg_controls AS (
>     SELECT * FROM d_pkg WHERE pkg_level = 'CRD'
>   )
>   -- select pieces that meeting pkg and pkg_level minimums
>   , min_pkgs_met AS (
>   ...
>   )
>   SELECT recid
>   FROM addresses [c]
>   JOIN min_pkgs_met [a] ON a.zip = c.zip AND a.crrt = a.crrt
> )

Without the database schema, this is impossible to test.

A CTE is allowed where a SELECT is allowed, so you have to use the
SELECT form of the INSERT statement:

 CREATE TABLE t(x);
 INSERT INTO t(x) SELECT 1;

 INSERT INTO t(x) WITH p AS (SELECT 1) SELECT * FROM p; -- or:
 WITH p AS (SELECT 1) INSERT INTO t(x) SELECT * FROM p;


Regards,
Clemens