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

  • Goalie35

    SSC Eights!

    Points: 854

    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

  • Ed Wagner

    SSC Guru

    Points: 286960

    To make sure I'm reading it correctly, you want to insert rows from UserRank into UserRankHistory that don't already appear in UserRankHistory. If I'm interpreting it correctly, something along these lines should give you what you're after:

    WITH cteNewRows AS (

    SELECT UserID, RankName

    FROM dbo.UserRanks

    EXCEPT

    SELECT UserID, RankName

    FROM dbo.UserRankHistory

    )

    INSERT INTO dbo.UserRankHistory(UserID, RankName, CreateDate)

    SELECT UserID, RankName, GETDATE()

    FROM cteNewRows;

    This is pretty simple, but because I don't have table DDL and sample data, I have to call it untested. Hope this helps.

  • The Dixie Flatline

    SSC Guru

    Points: 53231

    Just out of curiousity, shouldn't the date also be greater than the latest date for that user? Or is that not a concern because you're always insterting the current date?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • gvoshol 73146

    Hall of Fame

    Points: 3121

    Would it be possible to change the UserRank table to include a DateUpdated column? When a rank is added or changed, DateUpdated is set to GETDATE().

    Then from time to time troll through UserRank for records that were updated after the max date in the UserRankHistory table.

  • Goalie35

    SSC Eights!

    Points: 854

    Thanks for the help. I ended up taking a slightly different approach:

    INSERT INTO UserRankHistory(UserId, RankName)

    SELECT

    users.UserId, users.RankName

    FROM

    UserRankHistory

    RIGHT JOIN (select c.UserId as UserId, t.RankName as RankName

    FROM

    Users c

    INNER JOIN UserRanks t on t.UserId = c.UserId) users on users.UserId = UserRankHistory.UserId

    AND users.RankName = UserRankHistory.RankName

    WHERE

    UserRankHistory.UserId is null

    AND UserRankHistory.RankName is null;

    Thanks for all your help!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply