esProc Codes Dynamic MERGE statement

Databases, such as MSSQL and ORACLE, support updating tables using MERGE statement. But they lack functions for performing set operations. If data structure of the target table is unknown, it is very complicated to use the stored procedure to get its data structure and then compose the dynamic SQL statement. This may need scores of lines of code. For the same reason, it is also not easy to perform the operation in Java and other high-level languages. On the other hand, you must write the code into the database or the application when using stored procedures or the Java language, which is inconvenient for modification and management. In contrast, if esProc is used to help with the operation, the code can be database/application-independent and the architecture of the database or the application will be unaffected and easy to maintain.

Parameters source and target represent two tables with the same structure but different data. The source table will be used to update the target table based on their primary keys. For example, both Table 1 and Table 2 (as shown below) have a primary key consisting of column A and column B:

esProc_sql_dynamic_merge_1

Below is the MERGE statement for merging Table 1 with Table 2.

MERGE INTO table1 as t

USING table2 as s

ON t.A=s.A and t.B=s.B

WHEN MATCHED

THEN UPDATE SET t.C=s.C,t.D=s.D

WHEN NOT MATCHED

THEN INSERT VALUES(s.A,s.B,s.C,s.D)

The modified Table 1 will be as follows:

esProc_sql_dynamic_merge_2

esProc code

esProc_sql_dynamic_merge_3

 

A1,A2: Get the source table’s primary key from the system tables and store it in variable pks; the result is a set – [“A”,“B”]. Databases vary in how to get the primary key, here we’ll take MSSQL as an example.

A3,A4:Retrieve all columns from source, the result is [“A”,“B”,“C”,“D”].

A5:Compose the MERGE statement dynamically. pks.(…) is a loop function for computing members of a set (including the result set) in order. You can use ~ to reference the loop variable and # to reference the loop number in the computation.

A6:Execute the MERGE statement.

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in SQL-related Puzzle and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s