MS SQL Insert from CTE

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:

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:

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

Comments (2)