CODEWITHSUNDEEP
sqlsubqueryinsert-selectclockify
Syed ibrahim
Syed ibrahim

Reputation: 25

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

Upvotes: 1

Views: 94

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use not exists as follows:

 insert into Clockify(ClockifyId,DurationInMinutes,Date)
         select t.* from
          (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) ) t
     where not exists 
           (select 1 from Clockify c 
            where c.ClockifyId = t.ClockifyId
              and c.DurationInMinutes = t.DurationInMinutes
              and c.Date = t.Date) 
    -- remove the condition on column from here if you don't want to 
    -- consider column for finding duplicate

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

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)

Upvotes: 1

Related Questions