CTEs beyond SELECT

2

November 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
About these ads

2 thoughts on “CTEs beyond SELECT

  1. Ricky Lively says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 527 other followers

%d bloggers like this: