如果不是主键发生变化,merge into 的写法 MERGE INTO ODS USING STG ON ( ods.header_id = STG.header_id) WHEN MATCHED THEN UPDATE SET ODS.a=STG.a,ODS.b = STG.b WHEN NOT MATCHED THEN INSERT (header_id,a,b) VALUES(STG.header_id,STG.a,STG.b);如果真的是主键变化的话可以考虑这样做 DELETE FROM ODS WHERE NOT EXISTS (SELECT 1 FROM STG WHERE ods.header_id = STG.header_id);INSERT INTO ODS SELECT * FROM STG WHERE NOT EXISTS (SELECT 1 FROM ODS WHERE ods.header_id = STG.header_id);
可以利用sql语句生成代码的,如下面语句就可以一次性将某个表的字段全部提取出来的 select to_char(wmsys.wm_concat(column_name)) from user_tab_columns where table_name='你的表名'
MERGE INTO ODS
USING STG
ON ( ods.header_id = STG.header_id)
WHEN MATCHED THEN
UPDATE SET ODS.a=STG.a,ODS.b = STG.b
WHEN NOT MATCHED THEN
INSERT (header_id,a,b) VALUES(STG.header_id,STG.a,STG.b);如果真的是主键变化的话可以考虑这样做
DELETE FROM ODS
WHERE NOT EXISTS (SELECT 1 FROM STG WHERE ods.header_id = STG.header_id);INSERT INTO ODS
SELECT * FROM STG
WHERE NOT EXISTS (SELECT 1 FROM ODS WHERE ods.header_id = STG.header_id);
select to_char(wmsys.wm_concat(column_name)) from user_tab_columns where table_name='你的表名'