[Sqlalchemy-tickets] Issue #3581: MS SQL Insert from CTE (zzzeek/sqlalchemy)

[Johnathon H. <iss...@bi...>]

New issue 3581: MS SQL Insert from CTE
https://bitbucket.org/zzzeek/sqlalchemy/issues/3581/ms-sql-insert-from-cte

Johnathon Hege:

It appears that insert().from_select(), when passed a CTE, doesn't generate syntactically valid MS SQL code. The code it generates (using the recursive "parts" example in the CTE documentation) looks like:


```
#!sql

INSERT INTO destination (part, sub_part, quantity) WITH anon_1(part, sub_part, quantity) AS 
(SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS quantity 
FROM parts 
WHERE parts.part = ? UNION ALL SELECT parts_1.sub_part AS sub_part, parts_1.part AS part, parts_1.quantity AS quantity 
FROM parts AS parts_1, anon_1 AS anon_2 
WHERE parts_1.part = anon_2.sub_part)
 SELECT anon_1.part, anon_1.sub_part, anon_1.quantity 
FROM anon_1

```

But SQL Server requires the INSERT below the CTE definition, as below:


```
#!sql

WITH anon_1(part, sub_part, quantity) AS 
(SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS quantity 
FROM parts 
WHERE parts.part = ? UNION ALL SELECT parts_1.sub_part AS sub_part, parts_1.part AS part, parts_1.quantity AS quantity 
FROM parts AS parts_1, anon_1 AS anon_2 
WHERE parts_1.part = anon_2.sub_part)
INSERT INTO destination (part, sub_part, quantity)
 SELECT anon_1.part, anon_1.sub_part, anon_1.quantity 
FROM anon_1

```