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
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 code：
| A | |
| 1 | =myDB1.query(“select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE k where k.TABLE_NAME='”+source+”‘”) |
| 2 | =pks=A1.(COLUMN_NAME) |
| 3 | =myDB1.query(“select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME='”+source+”‘”) |
| 4 | =columns=A3.(COLUMN_NAME) |
| 5 | =” MERGE INTO “+target+” as t “+” USING “+source+” as s “+” ON “+pks.(“t.” + ~ + “=s.” + ~).string@d(” and “)+” WHEN MATCHED “+” THEN UPDATE SET “+(columns\pks).(“t.” + ~ +”=s.” + ~).string@d()+” WHEN NOT MATCHED “+” THEN INSERT VALUES( “+columns.(“s.”+ ~).string@d() +”)” |
| 6 | =myDB1.excute(A5) |
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.