Evaluate each field from source to target and update

• hegdesuchi

  Hall of Fame

  Points: 3047

  More actions

  October 13, 2016 at 7:30 am

  #97484

  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: 286590

  More actions

  October 13, 2016 at 7:54 am

  #1906324

  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

  More actions

  October 13, 2016 at 9:58 am

  #1906390

  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: 286590

  More actions

  October 13, 2016 at 10:12 am

  #1906394

  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: 42192

  More actions

  October 13, 2016 at 1:19 pm

  #1906457

  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

  More actions

  October 13, 2016 at 5:35 pm

  #1906484

  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: 1004363

  More actions

  October 13, 2016 at 6:21 pm

  #1906491

  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.

  Change is inevitable... Change for the better is not.
  

  ---

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

• hegdesuchi

  Hall of Fame

  Points: 3047

  More actions

  October 13, 2016 at 8:32 pm

  #1906496

  Hi,

  More than 40 columns.

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  October 14, 2016 at 4:22 am

  #1906523

  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.

  ---

  How to post data/code on a forum to get the best help.