Only insert records where the new value differs from the current value

1

How could I create a query that looks at a new batch of user data, compares it to the values that currently belong to each user & if the new data for a user is different, it inserts that new record into the table.

So just as an example, lets say, I have 3 tables (btw, I apologize if this scenario doesn't make much sense. My actual scenario deals with financial data that's difficult to explain. I thought this example may be a little clearer...maybe):

  • Users

  • UserRank

  • UserRankHistory

Users:
UserId | UserName

1 | John
2 | Amy
3 | George

UserRanks:
UserRankId | UserId | RankName

1 | 1 | Beginner
2 | 2 | Beginner
3 | 3 | Pro

UserRankHistory:
UserRankHistoryId | UserId | RankName | CreateDate

1 | 1 | Beginner | 2/3/2015
2 | 2 | Beginner | 6/19/2015
3 | 3 | Beginner | 11/12/2015
4 | 3 | Intermediate | 12/18/2015
5 | 3 | Pro | 4/3/2016

The UserRankHistory table keeps a record of the date when a user upgraded to the next rank. Every night, the Users & UserRanks tables are updated with new data. So I would need to compare what a user's latest RankName is in the UserRankHistory table & compare that against the rank currently in the UserRanks table. If it's different, then I add a new record into the UserRankHistory table.

Any idea on the best way to do this?

Thanks

2

Assuming the primary keys are identity, you could try this:

--insert into userrankhistory(userid,rankname,createdate) /* When you are happy with the result of the select, uncomment the insert */
select ur.userid
      ,ur.rankname
      ,cast(current_timestamp as date) createdate
  from userranks as ur
 where not exists (select 1
                     from (select urh1.rankname
                                 ,row_number() over(partition by urh1.userid
                                                    order by urh1.createdate desc
                                                            ,urh1.userrankhistoryid desc
                                                   )
                                  as rn
                             from userrankhistory as urh1
                            where urh1.userid=ur.userid
                          ) as urh2
                    where urh2.rn=1
                      and urh2.rankname=ur.rankname
                  )
;