CODEWITHSUNDEEP
sqlsql-serverinsert-update
FelipeFonsecabh
FelipeFonsecabh

Reputation: 187

Update or Insert using Select statement

I'm trying to write a query in SQL Server that does the following: perform a data aggregation query for one table and then pass the results to another table.

If a row with that date already exists, update the column, otherwise insert a new row

INSERT INTO T2 (timevalue, colValue)
    SELECT 
        Time_Stamp_Hour = DATEADD(hh, DATEPART(hh, E3TimeStamp), CAST(CAST(E3TimeStamp AS date) AS datetime)),
        AvgValue = AVG(MyValue)
    FROM
        (SELECT T1.*
         FROM T1
         WHERE (Quality) >= 0) t
    GROUP BY 
       DATEADD(hh, DATEPART(hh, E3TimeStamp), CAST(CAST(E3TimeStamp AS date) AS datetime))
       ON DUPLICATE KEY UPDATE  --TimeStamp matched
            colValue = t.AvgValue

However, I get a syntax error from this query:

Incorrect syntax the keyword 'ON'

I don't know what is wrong. Any ideas?

Upvotes: 0

Views: 89

Answers (1)

eshirvana
eshirvana

Reputation: 24568

SQL Server doesn't support upsert statements, instead you can use merge:

merge T2 as target
using ( 
select Time_Stamp_Hour=dateadd(hh,datepart(hh,E3TimeStamp), cast(CAST(E3TimeStamp as date) as datetime))
        , AvgValue=AVG(MyValue)
        from (select T1.*
                 from T1
                 where (Quality) >= 0)t
        group by dateadd(hh,datepart(hh,E3TimeStamp), cast(CAST(E3TimeStamp as date) as datetime))
) as source
on target.timevalue = source.Time_Stamp_Hour 
when not matched 
insert (Time_Stamp_Hour,AvgValue)
when matched 
update set target.colValue= source.AvgValue

Upvotes: 1

Related Questions