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.
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
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.
@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
T2.ID WHERE ( (T1.col1 & '|' & T1.col2 & '|' & T1.col3 ...) <> (T2.col1 & '|' & [T2].[col2] & '|' & T2.col3 ...) ) Thanks again.
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.