Evaluate each field from source to target and update

  • hegdesuchi

    Hall of Fame

    Points: 3047

    Hi,

    I have a request where i have to check if ID exists ,If ID exists then update. However client wants to evaluate each field from source and check if there are any changes in target, if there are any changes update only that field and rest of the fields should be original.

    So if my original request is,

    Source :

    ID name salary

    ID name company salary

    1 dave ABC 50000$

    2 ashley XXX 50000$

    Target:

    ID name company salary

    1 dave ABC 50000$

    2 ashley XXX 50000$

    Updated request:

    Source :

    ID name company salary

    1 dave ABC 80000$

    2 ashley XXX 80000$

    Now I have to check each column to see if there are any changes , only if any column has any change then update only that field.

    target:

    ID name company salary

    1 dave ABC 80000$

    2 ashley XXX 80000$

    I cannot use hashkey/flag field as my target table does not have these fields and I cannot alter them.

    What is the best way to do this?

    Any help is appreciated

  • Sean Lange

    SSC Guru

    Points: 286411

    You should look into MERGE. https://msdn.microsoft.com/en-us/library/bb510625.aspx

    And it is pointless endeavor to only update the columns that have changed. It will only succeed in making your queries slower because you will have to look at each and every column. An update to a table setting the value to the same value is not a big deal. Your client needs to understand that databases are a collection of sets of information. These sets are known as rows. You don't update part of the set, you update the set. Ideally you update the entire collection in one statement. Doing this as your client is requesting demonstrates a fundamental lack of knowledge about how databases work on their part.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • hegdesuchi

    Hall of Fame

    Points: 3047

    Thank you :-).

    I suggested same but they are asking to check individual column when there is no difference .

    Thanks again 🙂

  • Sean Lange

    SSC Guru

    Points: 286411

    hegdesuchi (10/13/2016)


    Thank you :-).

    I suggested same but they are asking to check individual column when there is no difference .

    Thanks again 🙂

    That is just plain absurd. What is going to be required is that you will have to first run a query to see if you need to update column1, then an update query for column1. You will have to repeat this for every column in the table. That is not how data should be handled at all, it is just plain awful. Checking each column to see if you should update is an exercise in futility. Good luck. You are not going be doing much actual sql with this client. It is mostly going to be writing RBAR (row by agonizing row) and CBAC (column by agonizing column) updates. In other words you are going to be doing awesome things like nested cursors instead of a single update statement. Hope they don't mind that their system is one of the slowest on the planet.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Chris Harshman

    SSC-Forever

    Points: 41703

    hegdesuchi (10/13/2016)


    Thank you :-).

    I suggested same but they are asking to check individual column when there is no difference .

    Thanks again 🙂

    If they're trying to avoid UPDATEs where there is no change, maybe if the table has a LastModifyDateTime in it, or if you could use hashing to compare the hash key values of the source and target. I've used these kind of techniques with a MERGE as Sean suggested to do UPDATEs effectively.

  • hegdesuchi

    Hall of Fame

    Points: 3047

    Hi,

    We cannot have hash because target table does not have it. we cannot alter the target table.

    Can we do this by dynamic updates?

    i.e. I want to check if any column has changes in target , update only those columns in target . can we do this dynamically?

  • Jeff Moden

    SSC Guru

    Points: 994293

    hegdesuchi (10/13/2016)


    Hi,

    I have a request where i have to check if ID exists ,If ID exists then update. However client wants to evaluate each field from source and check if there are any changes in target, if there are any changes update only that field and rest of the fields should be original.

    So if my original request is,

    Source :

    ID name salary

    ID name company salary

    1 dave ABC 50000$

    2 ashley XXX 50000$

    Target:

    ID name company salary

    1 dave ABC 50000$

    2 ashley XXX 50000$

    Updated request:

    Source :

    ID name company salary

    1 dave ABC 80000$

    2 ashley XXX 80000$

    Now I have to check each column to see if there are any changes , only if any column has any change then update only that field.

    target:

    ID name company salary

    1 dave ABC 80000$

    2 ashley XXX 80000$

    I cannot use hashkey/flag field as my target table does not have these fields and I cannot alter them.

    What is the best way to do this?

    Any help is appreciated

    How many columns to you have in the real tables?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • hegdesuchi

    Hall of Fame

    Points: 3047

    Hi,

    More than 40 columns.

  • DesNorton

    SSC-Insane

    Points: 22604

    hegdesuchi (10/13/2016)


    Hi,

    We cannot have hash because target table does not have it. we cannot alter the target table.

    Can we do this by dynamic updates?

    i.e. I want to check if any column has changes in target , update only those columns in target . can we do this dynamically?

    Create a hash in your select on both sides. Then compare the 2 hashes.

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

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