Detect if a new record is the same as the existing record in database

Question

Suppose there are some records in the database like this:

Id v1 v2 v3
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4

And suppose my new records look like this:
Id v1 v2 v3
1 1 2 1
5 5 5 5

Now, I want to insert my new records in this way:
1. Check if the Id exists in the database;
2. If it doesn't exist, insert the record directly;
3. If it exists, detect if the record is the same as the new one. If not, only update the changed values.

Since my real data is really large and contains 50+ variables and I want to make inserting fast, I would like to ask is there any general way to detect if a new record of same Id is different with the existing record in entity framework. I can't do 50+ if to see which variables have been changed.

Please make sure your solution is applicable to a large data set, Thanks.

Possible duplicate of [SQL Server Insert if not exist](http://stackoverflow.com/questions/20971680/sql-server-insert-if-not-exist) — Danieboy, Jul 25 '16 at 16:09
If the ID already exists, why would you need to check if 50+ fields are different? Do you have a specific performance problem, or are you just prematurely optimizing? — Paul Abbott, Jul 25 '16 at 16:09
`If it exists, detect if the record is the same as the new one. If not, only update the changed values`. You would get the same effect by replacing the entire old record with the new one — Matias Cicero, Jul 25 '16 at 16:11
@PaulAbbott, The thing is I want to update those fields with different values — cheddar, Jul 25 '16 at 16:12
@MatiasCicero, is there a better way to do that. I think if I update it no matter if it is changed, it will be slow if I have a lot of new record need to be inserted. Let's say suppose I got 2000 new records but just a few records are changed and this is exactly the situation I encounter now. Isn't it a waste of time to update all of them. — cheddar, Jul 25 '16 at 16:16
@Z.Chi If you really need to compare the old record with the new one, then you can hash both of them and compare the hashes. This won't tell you exactly which fields are different, but it'll tell you that your new record is different from the old one. Once you know this, you can replace the whole record. — Matias Cicero, Jul 25 '16 at 16:19
Sql server's has an `upsert` statement called merge. you might want to look that up. — Zohar Peled, Jul 25 '16 at 16:20
@Z.Chi - why don't you use the `MERGE` function - you only have to write it once - if it's a match then update otherwise insert. — SteveB, Jul 25 '16 at 16:21

score 0 · Accepted Answer · answered Jul 25 '16 at 16:23

You can use a MERGE to perform an "upsert" operation:

MERGE INTO #t AS TGT
USING ( VALUES
    (1,1,2,1),
    (5,5,5,5) ) AS SRC (Id, v1, v2, v3)
ON TGT.Id = SRC.Id
WHEN MATCHED THEN
    UPDATE SET TGT.v1 = SRC.v1,
               TGT.v2 = SRC.v2,
               TGT.v3 = SRC.v3
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, v1, v2, v3)
    VALUES (SRC.Id,
            SRC.v1,
            SRC.v2,
            SRC.v3);

Working example

BOL: https://msdn.microsoft.com/en-us/library/bb510625.aspx

Thank you Chris. I found `IDbSetExtensions.AddOrUpdate` Method does same function as `MERGE` in Entity Framework — cheddar, Jul 25 '16 at 20:57

Detect if a new record is the same as the existing record in database

1 Answers1