Using INSERT and/or UPDATE together from a single CTE

Question

I'm trying to query a cte using a cte called s4 and based on the results either insert or update to another table eventslog. 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.

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.

SQL;

 ,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 > 0 
       AND  k.groupid = l.groupid

Answer 1

I was working on something similar myself recently. I used a merge statement to handle doing an insert or update using a CTE.

Example below:

;WITH cte AS
(

   SELECT id,
          name
     FROM [TableA]
)

MERGE INTO [TableA] AS A
     USING cte
        ON cte.ID = A.id

      WHEN MATCHED 
      THEN UPDATE
       SET A.name = cte.name

      WHEN NOT MATCHED 
      THEN INSERT
    VALUES(cte.name);