question

tdwhite avatar image
tdwhite asked

Update only changed rows

I have a stored proc whose eventual purpose is to update Table 2 (in Database 2) with data from Table 1 (in Database 1). Right now it's updating 9 columns based on a join on the IDs of the two tables. Ideally I only want to update the rows if any of the data in those 9 columns are different. If everything is the same, then I don't want to do the update. However, I don't know which columns in Table 1 were changed. Is there any way to only update Table 2 if the relevant columns' data are different to those in Table 1? I'm using SQL Server 2000 Ent Edition. Thanks.
sql-server-2000update
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
azm avatar image
azm answered
You can use the CHECKSUM or BINARY_CHECKSUM function to do a comparison on the 9 columns. If you upgrade to SQL Server 2005+ then check out HASHBYTES but this won't work with the SQL 2000 installation. Difference between CHECKSUM and BINARY_CHECKSUM can be found here: [ https://decipherinfosys.wordpress.com/2007/05/18/checksum-functions-in-sql-server-2005/][1] Hashbytes link: [ https://msdn.microsoft.com/en-us/library/ms174415%28v=SQL.100%29.aspx][2] Try something like this: UPDATE T2 SET T2.col1 = T1.col1, T2.col2 = T1.col2, T2.col3 = T1.col3, ... FROM Table1 T1 INNER JOIN Table2 T2 on T1.ID = T2.ID AND CHECKSUM(T1.col1, T1.col2, T1.col3,...) <> CHECKSUM(T2.col1, T2.col2, T2.col3,...) Using not equal (<>) will return all the unchanged rows. [1]: https://decipherinfosys.wordpress.com/2007/05/18/checksum-functions-in-sql-server-2005/ [2]: https://msdn.microsoft.com/en-us/library/ms174415%28v=SQL.100%29.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Worked a treat! Thanks!!!
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
You could construct a query that checks for differences, but a diff in any column means that the whole update statement will run... update T2 set T2.col1 = T1.col1, T2.col2 = T1.col2, T2.col3 = T1.col3, ... from Table1 T1 join Table2 T2 on T1.Id = T2.ID where (T2.col1 <> T1.col1 or T2.col2 <> T1.col2 or T2.col3 <> T1.col3 or .... ) You could extend this idea and have 9 separate updates (1 for each column), but the performance overhead might be worse than the overhead of updating where not necessary. I don't know your data volumes, or why it is a problem that the updates are updating non-changed rows, so it's now up to you to test.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tdwhite avatar image
tdwhite answered
@Kev Riley - thanks for your answer. I later thought about creating a "composite" key with the relevant columns and doing the WHERE clause on that and this worked. I used the pipe character as a delimiter. UPDATE T2 SET T2.col1 = T1.col1, T2.col2 = T1.col2, T2.col3 = T1.col3, ... FROM Table1 T1 JOIN Table2 T2 on T1.ID = T2.ID WHERE ( (T1.col1 & '|' & T1.col2 & '|' & T1.col3 ...) <> (T2.col1 & '|' & [T2].[col2] & '|' & T2.col3 ...) ) Thanks again.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
Possibly a bit sledgehammer/nut, but you could also use Change Data Capture. This also depends on the software level of your instance. https://technet.microsoft.com/en-us/library/bb522489%28v=sql.105%29.aspx Another approach is to add a persisted calculated column to each table with a hash of the 9 columns. This then gets updated either at load or at update. Adding an index on this and the join key should make isolating your update set pretty speedy. But it has overhead, so you would have to figure out whether it was worth it.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.