The other day I was writing a query to pull some DMV information and I ended up using several CTEs in my query before I was done. My plan was to dump the information into a table to process it further. I’m used to just throwing an INSERT INTO command at the top of my SELECT so I was rather aggravated when I got the error:

 Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 2
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.

I had briefly forgotten that you can’t put anything before the WITH. I started to get a little panicky thinking I had just wasted my time writing this query, and was going to have to start over without CTEs when I realized I probably just needed to move my insert statement.

I’m going to borrow a CTE example from a blog by Robert Sheldon who appears to know quite a bit more about CTEs than I do. Not to mention the fact that I’m to lazy to write one for the purpose.

Here is the original query:

WITH 
cteTotalSales (SalesPersonID, NetSales)
AS
(
SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)
FROM Sales.SalesOrderHeader 
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
)
SELECT 
sp.FirstName + ' ' + sp.LastName AS FullName,
sp.City + ', ' + StateProvinceName AS Location,
ts.NetSales 
FROM Sales.vSalesPerson AS sp
INNER JOIN cteTotalSales AS ts
ON sp.BusinessEntityID = ts.SalesPersonID
ORDER BY ts.NetSales DESC

Here is the change I made to insert the data into a table:

WITH 
cteTotalSales (SalesPersonID, NetSales)
AS
(
SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)
FROM Sales.SalesOrderHeader 
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
)
INSERT INTO test
SELECT 
sp.FirstName + ' ' + sp.LastName AS FullName,
sp.City + ', ' + StateProvinceName AS Location,
ts.NetSales 
FROM Sales.vSalesPerson AS sp
INNER JOIN cteTotalSales AS ts
ON sp.BusinessEntityID = ts.SalesPersonID
ORDER BY ts.NetSales DESC

Notice that the INSERT statement is right below the CTE definition. According to BOL it will work with a SELECT, INSERT, UPDATE, MERGE or DELETE statement. I’ve tested this using UPDATE and SELECT … INSERT INTO statements and had no problems. I haven’t tried it with DELETEs because, to be honest, I can’t see a single reason why you would ever do it. If someone has one please comment and tell me, I’m seriously curious.

In addition the one time that I’ve found where code can go before the WITH (in the same statement) is in a CREATE VIEW. For example

CREATE VIEW Robert_Sheldons_CTE_Example AS
WITH 
cteTotalSales (SalesPersonID, NetSales)
AS
(
SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)
FROM Sales.SalesOrderHeader 
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
)
SELECT 
sp.FirstName + ' ' + sp.LastName AS FullName,
sp.City + ', ' + StateProvinceName AS Location,
ts.NetSales 
FROM Sales.vSalesPerson AS sp
INNER JOIN cteTotalSales AS ts
ON sp.BusinessEntityID = ts.SalesPersonID