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