title: SQL Server - How to use one CTE to do multiple queries?
body: <p>How can I use one cte for different queries. Here below an example where a cte is used to delete some records</p>
  
  <pre><code>    WITH CTE AS (
         SELECT FirstName, LastName, count(*) FROM Employee
         GROUP BY FirstName, LastName
         HAVING count(*) &gt; 1
      )
      DELETE FROM SomeOtherTable as sot
      WHERE sot.FirstName = cte.FirstName and sot.LastName = cte.LastName;
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>WITH CTE AS (
  SELECT FirstName, LastName, count(*) FROM Employee
  GROUP BY FirstName, LastName
  HAVING count(*) &gt; 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;
  </code></pre>
  
  <p>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.</p>