SQL server compare tables and update if changed

Question

Hi I am looking for A solution to update values in table1 only if the values change. I mean compare to tableb and update only the changed values

Answer 1

You could use a trigger on the source table that updates the target table.

However if there's large volume that could slow inserts/updates on the source quite badly. In which case, I'd make the trigger insert into a 3rd table. A scheduled job could then process that table and delete records (with appropriate use of transactions, of course).

An entirely different approach would be to move the triggering up one layer into your application, and use a message-based approach. In this situation you get the best of both worlds, because the process listening for messages will process them in order as fast as it can, leading to almost real-time updates of the target table.

So you can have your cake, and eat it.

Answer 2

An alternative to neils solution is to use binary checksum and store that in a field in your table then compare against that

Not saying its a better solution, just giving you some options.

Answer 3

for multiple rows at a time try:

UPDATE a
    SET IntCol=b.IntCol
       ,varcharCol=b.varcharCol
       ,DatetimeCol=b.DatetimeCol
    FROM TableA           a
        INNER JOIN (SELECT pk,IntCol,varcharCol,DatetimeCol FROM TableA
                    EXCEPT
                    SELECT pk,IntCol,varcharCol,DatetimeCol FROM TableB
                   ) dt ON a.pk=dt.pk