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

Question

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

Goalie35

SSC Eights!

Points: 871
More actions
August 27, 2016 at 2:17 pm

#320927

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

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

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

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

GaryV Hall of Fame Points: 3307 More actions · Answer 3

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: 871 More actions · Answer 4

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!