SQL Server - using the WITH clause in an INSERT statement

Question

I was wondering if this was possible. I have an existing query that uses the WITH clause to apply some aggregated data to a SELECT query like so: (massively simplified)

;WITH alias (y,z)
AS
(
    SELECT y,z FROM tableb
)
SELECT y, z FROM alias

I now want to INSERT the results of this query into another table.

I have tried the following:

INSERT INTO tablea(a,b)
;WITH alias (y,z)
AS
(
    SELECT y,z FROM tableb
)
SELECT y, z FROM alias

but I get the error:

Incorrect syntax near ';'.

So I have tried without the semicolon but got the error:

Incorrect syntax near the keyword 'WITH'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Is what I am trying to do possible with different some different syntax?

Answer 1

You will need to place the INSERT INTO right after the CTE. So the code will be:

;WITH alias (y,z)
AS
(
    SELECT y,z FROM tableb
)
INSERT INTO tablea(a,b)
SELECT y, z 
FROM alias

See SQL Fiddle with Demo