<p dir="auto"><strong>Migrated issue, originally created by Johnathon Hege</strong></p>
<p dir="auto">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:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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
"><pre class="notranslate"><code class="notranslate">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

</code></pre></div>
<p dir="auto">But SQL Server requires the INSERT below the CTE definition, as below:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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
"><pre class="notranslate"><code class="notranslate">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

</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">apparently its all possible now.  might as well make a new syntax bonanza out of it:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="WITH upsert AS (
  UPDATE metric k SET k.count = k.count + 5
  WHERE event = &quot;foo&quot; AND interval = &quot;D&quot; and date = &quot;whatever&quot;
  RETURNING k.*
)
INSERT INTO metric (event, interval, date, count) 
SELECT (&quot;foo&quot;, &quot;D&quot;, &quot;whatever&quot;, 5)
WHERE NOT EXISTS (
  SELECT 1 FROM upsert
);"><pre class="notranslate"><code class="notranslate">WITH upsert AS (
  UPDATE metric k SET k.count = k.count + 5
  WHERE event = "foo" AND interval = "D" and date = "whatever"
  RETURNING k.*
)
INSERT INTO metric (event, interval, date, count) 
SELECT ("foo", "D", "whatever", 5)
WHERE NOT EXISTS (
  SELECT 1 FROM upsert
);
</code></pre></div>
<p dir="auto"><a href="http://www.postgresql.org/docs/9.1/static/sql-insert.html" rel="nofollow">http://www.postgresql.org/docs/9.1/static/sql-insert.html</a></p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/2551/ticket_2551.patch">ticket_2551.patch</a> | <a href="../wiki/imported_issue_attachments/2551/CTE.sqlalchemy">CTE.sqlalchemy</a></p>