Stored procedure to update rows if exists

Question

I have a stored procedure that I am running to insert values if they don't exist and update if they do. I have a table where the data is coming from

Table1
Name (nvarchar)
Data (int)
Timestamp (datetime)

The data looks like this

Name1       5       2016-11-16 09:46:40.490
Name2       10      2016-11-16 09:48:35.240
Name1       7       2016-11-16 09:35:24.350
Name2       8       2016-11-15 02:27:44.670

I am trying to insert the names into a new table, average out the integers, and group by day. Here is the second table

Table2
Name (nvarchar)
Data (int)
Timestamp (date)

The data looks like this. Name1 was same day so it was averaged. Name2 was different days so they were not averaged.

Name1      6       2016-11-16 00:00:00.000
Name2      10      2016-11-16 00:00:00.000
Name2      8       2016-11-15 00:00:00.000

I converted the timestamp to date so it would be easier to combine on day. My stored procedure looks like this

IF NOT EXISTS (SELECT t.Name, t.Timestamp
    FROM Table2 t
    JOIN Table1 a
    ON t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp)
    GROUP BY t.Name, t.Timestamp)

INSERT INTO Table2 (Name, Timestamp, Data)
SELECT 
    Name,
    CAST(Timestamp AS DATE) as Date,
    AVG(Data) as Average_Data
FROM Table1
GROUP BY CAST(Timestamp AS DATE), Name

ELSE
UPDATE Table1
SET
WHERE

So I first time it runs, there is no issue. The values are added and grouped correctly. However, the second time it runs, it always inserts.

My update statement had looked something like this before I deleted it

SET Name = Name, Timestamp = Timestamp, Data = Date
WHERE Name = Name, Timestamp = Timestamp

I know this is not right but it never seems to even hit the Update as the Insert always runs. If I run the Select in the If Not Exists, I see data and it should see it as exists.

I guess I need help cleaning up the If Not Exists and creating a working Update statement.

EDIT:

Updated code below

IF EXISTS(SELECT Name, Timestamp FROM Table2)
UPDATE 
    Table2
SET 
    Name = a.Name,
    Timestamp = CONVERT(date, a.Timestamp),
    Data = AVG(a.Data)
FROM
    Table2 t
INNER JOIN
    Table1 a
ON t.Name = a.Name
WHERE t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp)

ELSE
    INSERT INTO Table2 (Name, Timestamp, Data)
        SELECT 
            Name,
            CAST(Timestamp AS DATE) as Date,
            AVG(Data) as Average_Data
        FROM Table1
        GROUP BY CAST(Timestamp AS DATE), Name
END

Answer 1

I personally prefer to use the older style technique for what is commonly known as an "upsert". MERGE works well but is a real pain to debug problems because it does everything in one shot.

Here is the type of approach I prefer because it separates the insert and the update which provides more flexibility and is easier to debug issues. You can also switch the left join in the insert to use a NOT EXISTS with a correlated subquery but most of the time the performance difference is pretty negligible.

UPDATE t
SET 
    Name = a.Name,
    Timestamp = CONVERT(date, a.Timestamp),
    Data = AVG(a.Data)
FROM
    Table2 t
INNER JOIN
    Table1 a
ON t.Name = a.Name
WHERE t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp)


INSERT INTO Table2 (Name, Timestamp, Data)
SELECT 
    Name,
    CAST(Timestamp AS DATE) as Date,
    AVG(Data) as Average_Data
FROM Table1 a
left join Table2 t on a.Name = t.Name
where t.Name is null
GROUP BY Name, CAST(Timestamp AS DATE)
    , Name

-- EDIT--

I didn't even notice the aggregate in your update when I posted. You can circumvent this easily with a cte.

with cte as
(
    select Name = a.Name
        , Timestamp = CONVERT(date, a.Timestamp)
        , AverageData = AVG(a.Data)
    FROM
        Table2 t
    INNER JOIN
        Table1 a
    ON t.Name = a.Name
    WHERE t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp)
    GROUP BY Name, CAST(Timestamp AS DATE)
)

update t
set Name = c.Name
    , Timestamp = c.Timestamp
    , Date = c.AverageData
from Table2 t
join cte c on c.Name = t.Name
    and c.Timestamp = t.Timestamp

Answer 2

I like to use the MERGE statement instead of consecutive UPDATE and INSERT. Because of the aggregation, in my answer below, I use a CTE with the MERGE.

-- CREATE AND INSERT TABLES 
DROP TABLE TABLE1
CREATE TABLE 
TABLE1
   (    Name nvarchar(5)
    ,   Data int
    ,   Timestamp date
    )
INSERT INTO TABLE1  VALUES  ('Name1',5  ,'2016-11-16')
                    ,       ('Name2',10 ,'2016-11-16')
                    ,       ('Name1',7  ,'2016-11-16')
                    ,       ('Name2',8  ,'2016-11-15')
                    ,       ('Name3',8  ,'2016-11-15')
                    ,       ('Name3',10 ,'2016-11-15')
                    ,       ('Name3',9  ,'2016-11-16')
                    ,       ('Name3',11 ,'2016-11-16')


DROP TABLE TABLE2
CREATE TABLE 
TABLE2
   (    Name nvarchar(5)
    ,   Data int
    ,   Timestamp datetime
    )
INSERT INTO TABLE2 VALUES  ('Name1',0 ,'2016-11-16')
                          ,('Name2',0 ,'2016-11-16')
                          ,('Name2',0 ,'2016-11-15')

    SELECT * FROM TABLE2 -- SHOW TABLE2 BEFORE MERGE


BEGIN TRANSACTION
--  HERE IS WHERE THE CODE THAT REPLACES YOUR QUERY ACTUALLY BEGINS
   ;WITH    CTE_AVG_DATA_TABLE1
        AS (SELECT      Name 
            ,           Timestamp
            ,           AVG(Data) AS [AVG(Data)]
            FROM        TABLE1
            GROUP BY    Name 
            ,           Timestamp
            )

        MERGE   Table2              AS TARGET
        USING   CTE_AVG_DATA_TABLE1 AS SOURCE
            ON      TARGET.Name = SOURCE.Name
                AND TARGET.TIMESTAMP = SOURCE.TIMESTAMP
        WHEN MATCHED THEN 
            UPDATE      
            SET     TARGET.Name         = SOURCE.Name
            ,       TARGET.Timestamp    = CONVERT(date, SOURCE.Timestamp)
            ,       TARGET.Data         = SOURCE.[AVG(Data)]
        WHEN NOT MATCHED THEN 
            INSERT (Name, Timestamp, Data)
            VALUES (SOURCE.NAME, SOURCE.TIMESTAMP, SOURCE.[AVG(Data)])
        ;

    SELECT * FROM TABLE2 -- SHOW TABLE2 AFTER MERGE
ROLLBACK /*ALLOWS THIS TEST CODE TO BE RUN OVER AND OVER 
           ADDING ROWS TO INSERT STATEMENTS ABOVE TO SHOW THAT IT WORKS
         */