2
0
Last week, I introduced a stored procedure called DYNAMIC_MERGE, which dynamically retrieved column names from a staging table and used them to construct a MERGE INTO statement. While this approach offered flexibility, it had a limitation – the HASH condition used static column names. Hence relying on static column names, limiting the procedure’s adaptability across different tables. In extension to this functionality, making it even more dynamic and flexible I developed the new procedure DYNAMIC_MERGE_NEW.
With this enhancement, we’ve eliminated the need for static column names in the HASH condition, making the procedure more adaptable to varying table structures. Now, regardless of the table input, the procedure dynamically retrieves column names and incorporates them into the HASH condition, ensuring a seamless and efficient merge process.
Key Improvements:
1.Dynamic Column Calculation: The procedure now dynamically calculates the columns for the hash condition, allowing for more flexible and adaptive merging across diverse table structures.
2.Enhanced Flexibility: By eliminating static column names in the hash condition, the procedure can seamlessly accommodate changes in table structures without requiring manual adjustments.
Stored Procedure:
Say we have below two tables with different structure and as part of requirement we have to perform MERGE from source to Target table. The procedure takes the table names as arguments, and the procedure itself manages the remaining logic.
Also find below the count in target tables:
Run the Proc with Table as input argument:
call DYNAMIC_MERGE_new('ORDER_DETAIL','ORDER_DETAIL_TARGET');
call DYNAMIC_MERGE_new('S_INVOICE','S_INVOICE_TARGET');
Verify the output:
