October 14, 2016 at 3:35 pm
Hi All,
I have to update target table and I am good with below logic.
MERGE testdb.dbo.targettable r
using dbo.sourcetable s
on s.id=r.id
WHEN MATCHED THEN
UPDATE
SET column1 = CASE WHEN r.column1 <> s.column1 THEN s.column1 ELSE r.column1 END,
column2 = CASE WHEN r.column2 <> s.column2 THEN s.column2 ELSE r.column2 END,
column3 = CASE WHEN r.column3 <> s.column3 THEN s.column3 ELSE r.column3 END,
column4 = CASE WHEN r.column4 <> s.column4 THEN s.column4 ELSE r.column4 END,
column5 = CASE WHEN r.column5 <> s.column5 THEN s.column5 ELSE r.column5 END,
column6 = CASE WHEN r.column6 <> s.column6 THEN s.column6 ELSE r.column6 END
and many more ;
I was looking if there is any query that can be written dynamically i.e. dynamically checking if any column needs update from source to target and update the target tables rather than checking each column.
any help is appreciated!
October 15, 2016 at 2:36 am
hegdesuchi (10/14/2016)
Hi All,I have to update target table and I am good with below logic.
MERGE testdb.dbo.targettable rusing dbo.sourcetable s
on s.id=r.id
WHEN MATCHED THEN
UPDATE
SET column1 = CASE WHEN r.column1 <> s.column1 THEN s.column1 ELSE r.column1 END,
column2 = CASE WHEN r.column2 <> s.column2 THEN s.column2 ELSE r.column2 END,
column3 = CASE WHEN r.column3 <> s.column3 THEN s.column3 ELSE r.column3 END,
column4 = CASE WHEN r.column4 <> s.column4 THEN s.column4 ELSE r.column4 END,
column5 = CASE WHEN r.column5 <> s.column5 THEN s.column5 ELSE r.column5 END,
column6 = CASE WHEN r.column6 <> s.column6 THEN s.column6 ELSE r.column6 END
--and many more ;
I was looking if there is any query that can be written dynamically i.e. dynamically checking if any column needs update from source to target and update the target tables rather than checking each column.
any help is appreciated!
I think this is not the correct approach, for every match update column to either new or existing value no matter what, much better to use a plain update statement updating all the columns all the time but I guess that is not what you are after.
😎
Using the conditional within the MATCH clause will avoid those unnecessary updates
MERGE testdb.dbo.targettable r
using dbo.sourcetable s
on s.id=r.id
WHEN MATCHED
AND
(
r.column1 <> s.column1
OR
r.column2 <> s.column2
OR
r.column3 <> s.column3
OR
r.column4 <> s.column4
OR
r.column5 <> s.column5
OR
r.column6 <> s.column6
)
THEN
UPDATE
SET column1 = s.column1
,column2 = s.column2
,column3 = s.column3
,column4 = s.column4
,column5 = s.column5
,column6 = s.column6
and many more ;
October 15, 2016 at 9:27 am
Hi ,
Thank you!yes I agree with you! My client wants to check if columns are same .update only if they are not same. I could think only of this way. Was wondering if there are any dynamic queries to update.
October 17, 2016 at 8:48 am
hegdesuchi (10/15/2016)
Hi ,Thank you!yes I agree with you! My client wants to check if columns are same .update only if they are not same. I could think only of this way. Was wondering if there are any dynamic queries to update.
Your client doesn't know what he is talking about. This can and should be done as a single operation updating all relevant columns regardless of whether there is an actual change in that specific column. Under your client's approach, you would need to run it at least once for each possible combination of changed columns (2n - 1 times) and would potentially end up running it RBAR. On top of that, you open yourself up to SQL injection.
Now, it's is easy to eliminate ROWS where NONE of the columns have changed, and you can and should do that, too. Given the number of columns, I would suggest using the EXCEPT clause, especially if you have to deal with nullable columns.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 17, 2016 at 9:01 am
thank you very much!
How can i achieve that using except clause?
October 17, 2016 at 9:23 am
drew.allen (10/17/2016)
Your client doesn't know what he is talking about
Careful now... if there are column-based audit or alert triggers on the table, then the client DOES know what they're talking about. And done correctly, there's no chance of SQL Injection for the dynamic SQL to solve this problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2016 at 9:28 am
drew.allen (10/17/2016)
hegdesuchi (10/15/2016)
Hi ,Thank you!yes I agree with you! My client wants to check if columns are same .update only if they are not same. I could think only of this way. Was wondering if there are any dynamic queries to update.
Your client doesn't know what he is talking about. This can and should be done as a single operation updating all relevant columns regardless of whether there is an actual change in that specific column. Under your client's approach, you would need to run it at least once for each possible combination of changed columns (2n - 1 times) and would potentially end up running it RBAR. On top of that, you open yourself up to SQL injection.
Now, it's is easy to eliminate ROWS where NONE of the columns have changed, and you can and should do that, too. Given the number of columns, I would suggest using the EXCEPT clause, especially if you have to deal with nullable columns.
Drew
It's probably best to include it in your source table definition, I'll leave it to you to figure out how to do that. You can also include it in your WHEN clause like so
WHEN MATCHED AND EXISTS (
SELECT tgt.column1, tgt.column2, tgt.column3, tgt.column4
EXCEPT
SELECT src.column1, src.column2, src.column3, src.column4
)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 17, 2016 at 9:31 am
hegdesuchi (10/17/2016)
thank you very much!How can i achieve that using except clause?
EXCEPT will only produce a list of rows that aren't the same. Sure, it's a good start but it won't help with the final check.
Since you're being paid to do this and then will also be paid to support it, I recommend you hit the books. Lookup the EXCEPT operator and read what it does and lookup how to read the column metadata from the system tables so that you can use that metadata to drive your dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply