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:


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





The modified Table 1 will be as follows:


esProc code



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.


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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s