CTEs beyond SELECT
5November 20, 2012 by Kenneth Fisher
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
you can put statements BEFORE with… you just have to terminate them with “;” (in the future it will be required to terminate all SQL commands with “;”).
ie.
select 1;
with cte…
Sorry, I worded that less than clearly. I meant that nothing goes before the WITH within the same statement.
So
SELECT 1;
WITH cte …
but not
INSERT INTO testtable
WITH cte …
and not
WITH cte1 ..
SELECT * FROM cte1
UNION ALL
WITH cte2 ..
SELECT * FROM cte2
[…] somewhat. Among other things this confusion leads to difficulty understanding how to use them with queries other than SELECT or how to use them beyond a single CTE at a […]
[…] well known is that you can actually update the data within the CTE. No, I don’t mean using using the UPDATE statement with a CTE but actually running the update through the […]
I have used CTEs with a DELETE when cleaning up duplicate entries of tables (and then adding the missing uniqueness constraint, of course).
WITH myCTE AS (
SELECT colA
, colB
, rn = ROW_NUMBER () OVER (PARTITION BY colA, colB)
FROM myTable
)
DELETE myCTE
WHERE rn > 1;