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?
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