【Question】

Hello everyone,
I have one query, would be great if anyone can help me out on this.
In SQL, I have two tables with same column names. Want to query if there is any difference in the column values and if yes will update the values (in the first table) else if the row is not found will insert the row using the MERGE statement.
As of now, I have to mention all the columns in the tables and match by the values.
Eg: If the tables are like this:
Table1 (A,B,C,D...)
Table2 (A,B,C,D...)
The query would be like this :
MERBE INTO Table1 as TabA
USING (Select * from Table2) AS TabB
ON TabA.A=TabB.A
AND TabA.B=TabB.B
AND TabA.C=Tab.C
AND TabA.D=TabB.D
...
...
When Matched Then
Update
Set TabA.A=TabB.A,
, TabA.B=TabB.B
, TabA.C=TabB.C
, TabA.D=TabB.D
..
..
..
When NOT Matched Then
Insert Values (TabB.A,TabB.B,TabB.C, TabB.D..)
But I want the column names to be fetched dynamically so that every time the column name changes or a new column is added, we don’t have to rewrite the query (considering there are a lot of columns in the tables to be matched).

【Answer】

Writing a MERGE statement dynamically is very inconvenient with the stored procedure if the table’s structure is inexplicit. The process will be much simpler if we handle this in SPL (Structured Process Language).

First define two parameters, source and target, to reference table name dynamically.

A1: Get source table’s primary key from the system table. Different databases have their own way to get the primary key. Here we take MSSQL as an example.

A2: Store the sequence of primary key fields in variable pks, which is in the form of ["A","B"].

A3: Get all columns of source.

A4: Store the sequence of column names in variable columns, which is in the form of ["A","B","C","D"].

A5: Compose a dynamic MERGE statement.

pks.("t." + ~ + "=s." + ~).concat("and") loops over members of pks’s sequence to join up processed members into a string by and, which is in the format of t.A=s.A and t.B=s.B.

(columns\pks).("t." + ~ +"=s." + ~).concat@c()first gets difference between columns and pks, which is a sequence of non-primary-key fields, and then runs a loop to process members of the sequence and join them up into a string by commas, which is in the format of t.C=s.C,t.D=s.D.

Likewise, columns.("s."+ ~).concat@c()composes a string in the format of s.A,s.B,s.C,s.D.

A6: Execute A5’s merge statement.