sqlsubqueryinsert-selectclockify

Remove dupilcates query in INSERT SELECT statement


I have a query which is inserting data into Clockify Table from Task Table through INSERT SELECT statements.

ALTER procedure [dbo].[ClockifyAdd]  
 AS  
 BEGIN  
       insert into Clockify(ClockifyId,DurationInMinutes,Date)
         SELECT 
              ClockifyId
            , SUM(DATEDIFF(mi, CAST(StartTime AS datetime), CAST(EndTime AS 
               datetime))) AS DurationInMinutes
            , CAST(StartTime AS date) AS Date
        FROM Task
        GROUP BY
              ClockifyId
            , CAST(StartTime AS date)
END  

The problem is while inserting into Clockify table I don't want to insert duplicates and put a check here. I have a stored procedure to remove duplicates but I want a better approach that while inserting there must be a check for duplicates and if there is a duplicate it must not insert. Please look image for better understanding Thanks in advance for replying and taking out your precious time to address this issue. enter image description here


Solution

  • You could check if value is already prensent uning a left join anche check for null value

    insert into Clockify(ClockifyId,DurationInMinutes,Date)
         SELECT 
              Task.ClockifyId
            , SUM(DATEDIFF(mi, CAST(Task.StartTime AS datetime), CAST(Task.EndTime AS 
               datetime))) AS DurationInMinutes
            , CAST(Task.StartTime AS date) AS Date
        FROM Task
        LEFT JOIN Clockify 
            ON Task.ClockifyId = Clockify.ClockifyId 
                AND Task.Date = Clockify.Date
        WHERE Clockify.ClockifyId Is NULL
        GROUP BY
              Task-ClockifyId
            , CAST(Task.StartTime AS date)