动态拼接 merge 语句
【问题】
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 everytime the column name changes or a new column is added , we dont have to rewrite the query(considering there are a lot of columns in the tables to be matched)
Please let me know if you want me to rephrase my sentence to make my statement clear .
【回答】
表结构未知时，依靠存储过程拼出动态的MERGE语句非常麻烦，如果用SPL来拼，脚本要短许多：
首先定义参数source和target，便于动态指定表名
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." + ~).concat("and")+ "WHEN MATCHED"+ "THEN UPDATE SET "+(columns\pks).("t." + ~ +"=s." + ~).concat@c()+ "WHEN NOT MATCHED"+ "THEN INSERT VALUES( "+columns.("s."+ ~).concat@c()+")" |
6 |
=myDB1.excute(A5) |
A1：从系统表获取source表的主键，每个数据库获取主键方式不同，这里以MSSQL为例
A2：将主键组成的序列保存到变量pks中，形如["A","B"]
A3：检索source所有列
A4：将列名组成的序列保存到变量columns中，形如["A","B","C","D"]
A5：动态拼接merge语句
pks.("t." + ~ + "=s." + ~).concat("and")，对序列pks循环计算，将计算后的序列成员拼成and连接的字符串，形如：t.A=s.A and t.B=s.B
(columns\pks).("t." + ~ +"=s." + ~).concat@c()，首先针对columns和pks求差列，获取非主键字段组成的序列，再对该序列循环计算，将计算后的序列成员拼成逗号连接的字符串，形如：t.C=s.C,t.D=s.D
columns.("s."+ ~).concat@c()同理拼成形如s.A,s.B,s.C,s.D的字符串
A6：执行A5返回的merge语句
