title: Using INSERT and/or UPDATE together from a single CTE
body: <p>I'm trying to query a cte using a cte called <code>s4</code> and based on the results either insert or update to another table <code>eventslog</code>. I was unable to do this in one single query and needed to insert the data first in a temp table. I would like to get rid of insertion to a temp table part and just insert or update it directly. </p>
  
  <p>I think I was having issues with calling that cte more than once. Is there a work around? How can I either insert or update into a table by querying a single cte? Any help is most appreciated. </p>
  
  <p>SQL;</p>
  
  <pre><code> ,ss4 
   AS (SELECT DISTINCT h.groupid, 
                       h.eventid, 
                       Sum(h.vcheck) AS tot ,
                       max(h.eventtime) as eventtime
       FROM   ss3 h 
       GROUP  BY h.groupid, 
                 h.eventid
                 ) 
  INSERT INTO @glo 
              (eventtime, 
               eventid, 
               groupid, 
               vcheck) 
  SELECT DISTINCT i.eventtime, 
                  i.eventid, 
                  i.groupid, 
                  i.tot 
  FROM   ss4 i 
  
  
  INSERT INTO eventslog 
              (eventtime, 
               eventid, 
               groupid) 
  SELECT DISTINCT j.eventtime, 
                  j.eventid, 
                  j.groupid 
  FROM   @glo j 
  WHERE    
    j.vcheck = 0 
    AND NOT EXISTS(SELECT eventid 
                   FROM   eventslog 
                   WHERE  eventid = j.eventid 
                          AND groupid = j.groupid 
                          AND clearedtime IS NULL)   
  
  
  UPDATE k 
  SET    k.clearedtime = l.eventtime 
  FROM   eventslog k 
         RIGHT JOIN @glo l 
                 ON k.groupid = l.groupid 
                    AND k.eventid = l.eventid 
  WHERE  l.vcheck &gt; 0 
         AND  k.groupid = l.groupid
  </code></pre>