Remove dupilcates query in INSERT SELECT statement

Question

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

Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product (and your code is non-standard SQL). Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Feb 05 '21 at 11:46

score 1 · Accepted Answer · answered Feb 05 '21 at 11:44

1

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)

answered Feb 05 '21 at 11:44

ScaisEdge

131,976
10
91
107

Yes it is working, now I'am getting unique rows everytime. Thanks scaisEdge. Appreciated – Syed ibrahim Feb 05 '21 at 12:10
@Syedibrahim well if my answer is right please (past 15 minutes) mark it as accepted ...see how here http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – ScaisEdge Feb 05 '21 at 12:19
Yes sure, I learned how to accept and I have accepted the answer. Thanks – Syed ibrahim Feb 07 '21 at 09:47
...@Syedibrahim ... .you comment seems weird – ScaisEdge Feb 07 '21 at 10:00
haha!! I'am new to stackoverflow. Learning how to use it :p – Syed ibrahim Feb 07 '21 at 10:05
what if I want to update the record if it exists in the above query.? – Syed ibrahim Feb 07 '21 at 10:13
depend on the db you are really using (sql is a query language not a db) anyway You could try using a constrain .ON INSER UPDATE .. or a separated query base on an update and join with the suibquery .. for this you shoudl post a new question properly documented .. – ScaisEdge Feb 07 '21 at 10:52

score 0 · Answer 2 · answered Feb 05 '21 at 11:46

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

Thanks Popeye this query is also giving desired results. Appreciated — Syed ibrahim, Feb 05 '21 at 12:10

Remove dupilcates query in INSERT SELECT statement

2 Answers2