SQL Server - How to use one CTE to do multiple queries?

Question

How can I use one cte for different queries. Here below an example where a cte is used to delete some records

    WITH CTE AS (
       SELECT FirstName, LastName, count(*) FROM Employee
       GROUP BY FirstName, LastName
       HAVING count(*) > 1
    )
    DELETE FROM SomeOtherTable as sot
    WHERE sot.FirstName = cte.FirstName and sot.LastName = cte.LastName;

I would like to know how to use this cte to additional queries without of having to rewrite the cte. I here below what I am trying to achieve but which of course doesn't work.

WITH CTE AS (
SELECT FirstName, LastName, count(*) FROM Employee
GROUP BY FirstName, LastName
HAVING count(*) > 1
)
DELETE FROM SomeOtherTable as sot
WHERE sot.FirstName = cte.FirstName and sot.LastName = cte.LastName;
INSERT INTO PeopleTable (FirstName,LastName) 
SELECT cte.FirstName, cte.LastName from cte;

The query describes are just an example ! The question is how to use one cte to do multiple queries? Please do not propose to duplicate the cte. This is exactly what I am trying to avoid. TY very much in advance for the help.

A CTE explicitly only exists for the duration of a Single statement. What you described is essentially a temporary view, and they don't exist. Sorry, the manual says No. What you MAY be able to do is an INSERT and a DELETE in a single statement using MERGE or OUTPUT, or write a DELETE that leaves exactly one copy of each 'person' *(using ROW_NUMBER())* — MatBailie, Jul 12 '15 at 22:26
You want a temporary table or table variable, not a CTE. You probably also want to wrap the statements in a transaction. — Gordon Linoff, Jul 12 '15 at 22:27
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx — MatBailie, Jul 12 '15 at 22:32

Fabio · Accepted Answer · 2015-07-13T04:43:34.487

Use TABLE variable and OUTPUT Clause (Transact-SQL)

DECLARE @Deleted TABLE
(
    FirstName VARCHAR(255),
    LastName VARCHAR(255)
)

WITH CTE AS (
    SELECT FirstName, LastName, count(*) FROM Employee
    GROUP BY FirstName, LastName
    HAVING count(*) > 1
)
DELETE FROM SomeOtherTable as sot
OUTPUT deleted.FirstName, deleted.LastName INTO @Deleted
WHERE sot.FirstName = cte.FirstName and sot.LastName = cte.LastName;

INSERT INTO PeopleTable (FirstName,LastName) 
SELECT FirstName, LastName from @Deleted;

Or, even better, as @Hart suggested in the comments insert OUTPUT values straight to another table

WITH CTE AS (
    SELECT FirstName, LastName, count(*) FROM Employee
    GROUP BY FirstName, LastName
    HAVING count(*) > 1
)
DELETE FROM SomeOtherTable as sot
OUTPUT deleted.FirstName, deleted.LastName INTO PeopleTable (FirstName,LastName) 
WHERE sot.FirstName = cte.FirstName and sot.LastName = cte.LastName;

What's your reason for outputting to a table variable instead of straight into the table? — Hart CO, Jul 13 '15 at 01:59

SQL Server - How to use one CTE to do multiple queries?

1 Answers1