0

I am using the following update query , I need to insert the same data, if the update query fails. How can I traverse and check the status of update and if fail updation insert the values to the table...please help me 

 With CTE AS 
    (
        SELECT CONVERT(DATETIME, CONVERT(VARCHAR, new_time, 101)) As day,
               DATEPART(hh,new_time) As hour,
               COUNT(*) As Total
        FROM log_table 
        WHERE new_time > GETDATE() - 180
        GROUP BY  CONVERT(DATETIME, CONVERT(VARCHAR, new_time, 101)),
                  DATEPART(dd,new_time),
                  DATEPART(hh,new_time)
    )
    UPDATE tmp_table
    SET Count= CTE.Total
    FROM tmp_table INNER JOIN CTE
         ON temp_table.date=cte.day AND temp_table.hour=cte.hour
chinnusaccount
  • 239
  • 6
  • 20
  • You can use `merge` instead of `update` and `insert` - it allows you to do both at once, depending on the need. However, it's not entirely concurrency-safe, and has some limitations compared to just using insert/update. – Luaan Jun 16 '15 at 12:16
  • 2
    possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) – Zsuzsa Jun 16 '15 at 12:18
  • You have a probable bug in your code, given you group by day, but don't select it out. Also, doing the grouping that way means the optimizer can't use an index, which could be expensive; you might get better results by building a range table (with start/next-start timestamp columns). – Clockwork-Muse Jun 16 '15 at 12:45

0 Answers0