Generate a Merge Statement on IBM iSeries DB2UDB.

GenerateMerge.SQL

-- Change THETABLENAME to the table you want to reference. It must already exist.
WITH T1 ( Table_Name, Table_Prefix, Table_Prefix2) As ( values( Ucase('THETABLENAME'), 'T1.', 'T2.' ) )


, I1 (INDEX_NAME, INDEX_SCHEMA, Table_Name, Table_Prefix, Table_Prefix2 ) as (
	Select I1.INDEX_NAME, I1.INDEX_SCHEMA, t1.Table_Name, t1.Table_Prefix, t1.Table_Prefix2 
	From SYSINDEXES I1 
	Join T1 Using (Table_Name) 
	ORDER BY CASE
		WHEN IS_UNIQUE = 'U' then 1 
		WHEN IS_UNIQUE = 'E' then 9 
		WHEN IS_UNIQUE = 'D' then 2 
		ELSE 3 END,
	I1.Index_name 
	Fetch First 1 Rows Only  
)
, I1_KEYS (TB_Name,INDEX_NAME, INDEX_SCHEMA, KeyList, T1_EQUALS_T2) AS ( 
	Select Table_Name,INDEX_NAME, INDEX_SCHEMA, 
		LISTAGG( ALL Table_Prefix || COLUMN_NAME ||'='||Table_Prefix2 || COLUMN_NAME, ',') 
			WITHIN GROUP ( ORDER BY ORDINAL_POSITION ASC),
		LISTAGG( ALL Table_Prefix || COLUMN_NAME ||'='||Table_Prefix2 || COLUMN_NAME, ' and ')
			WITHIN GROUP ( ORDER BY ORDINAL_POSITION ASC)  
	From I1 
	JOIN SYSKEYS USING (INDEX_NAME, INDEX_SCHEMA)
	GROUP BY TABLE_NAME, INDEX_NAME, INDEX_SCHEMA 
) 
, T2(TABLE_NAME, TABLE_SCHEMA, Is_Identity, COL_COUNT, COL_LIST, COALESCE_LIST, T1_LIST, T2_LIST, T1_SUBTRACT_T2, T1_EQUAL_T2, T1_SET_T2)
AS (
	SELECT TABLE_NAME, TABLE_SCHEMA, 
		Max(Is_Identity), 
		COUNT( DISTINCT COLUMN_NAME),
		LISTAGG( ALL COLUMN_NAME , chr(13)||',') WITHIN GROUP ( ORDER BY ORDINAL_POSITION ASC),  
		LISTAGG( ALL 'Coalesce('|| Table_Prefix || COLUMN_NAME ||',0), ') WITHIN GROUP ( ORDER BY ORDINAL_POSITION ASC),
		LISTAGG( ALL Table_Prefix || COLUMN_NAME  , chr(13) || ',') WITHIN GROUP ( ORDER BY ORDINAL_POSITION ASC),  
		LISTAGG( ALL Table_Prefix2 || COLUMN_NAME , chr(13) || ',') WITHIN GROUP ( ORDER BY ORDINAL_POSITION ASC),  
		LISTAGG( ALL Table_Prefix || COLUMN_NAME ||'-'||Table_Prefix2 || COLUMN_NAME, ',') WITHIN GROUP ( ORDER BY ORDINAL_POSITION ASC),  
		LISTAGG( ALL Table_Prefix || COLUMN_NAME ||'='||Table_Prefix2 || COLUMN_NAME, ' AND ') WITHIN GROUP ( ORDER BY ORDINAL_POSITION ASC),
		LISTAGG( ALL Table_Prefix || COLUMN_NAME ||'='||Table_Prefix2 || COLUMN_NAME , chr(13)  || ',') WITHIN GROUP ( ORDER BY ORDINAL_POSITION ASC)
	FROM SYSCOLUMNS2 T0 
	JOIN T1 USING (Table_Name)
	GROUP BY TABLE_NAME, TABLE_SCHEMA
)
, FieldList (TABLE_SCHEMA, TABLE_NAME, FIELD_LIST) as (
	SELECT TABLE_SCHEMA, TABLE_NAME, 
		LISTAGG( 'Cast(? as ' || 
			CASE 
				WHEN DATA_TYPE='DECIMAL' THEN 'Decimal(' || TRIM(CHAR(LENGTH)) || ',' || TRIM(CHAR(numeric_scale)) || ')' 
				WHEN DATA_TYPE ='CHAR' THEN 'Char(' || TRIM(CHAR(LENGTH)) || ')'  
				WHEN DATA_TYPE ='VARCHAR' THEN 'Varchar(' || TRIM(CHAR(LENGTH)) || ')'  
				WHEN DATA_TYPE ='TIMESTMP' THEN 'Timestamp'  
				ELSE DATA_TYPE || '' end 
			|| ')'
			, chr(13) || ',' ) 
			WITHIN group(ORDER BY T1.ORDINAL_POSITION)
	FROM SYSCOLUMNS T1 
	CROSS JOIN (VALUES(0, 'F0')) T2 (StartIndicator, FieldPrefix)
	Group by TABLE_SCHEMA, TABLE_NAME
)
SELECT 
'MERGE INTO ' || T2.TABLE_SCHEMA || '/' || T2.TABLE_NAME ||
' T1 USING ( VALUES (' || chr(13) || ' ' || FIELD_LIST|| 
chr(13) || ')) T2 ('||COL_LIST || ')ON ' || COALESCE( IKEYS.T1_EQUALS_T2, 'T1=T2')  ||
chr(13) || ' WHEN NOT MATCHED THEN INSERT (' || T1_LIST || ')' || 
case when Is_Identity ='YES' Then ' Overriding user value' Else '' End || 
chr(13) || ' VALUES (' || T2_LIST || ')' ||
chr(13) || ' WHEN MATCHED THEN UPDATE' || 
case when Is_Identity ='YES' Then ' Overriding System value' Else '' End ||  
' SET ' ||  T1_SET_T2 ||
chr(13) || ' ELSE IGNORE'

FROM T2
Left Join FieldList ON FieldList.TABLE_SCHEMA = T2.TABLE_SCHEMA AND FieldList.TABLE_NAME = T2.TABLE_NAME

Left Join I1_KEYS IKEYS ON IKEYS.TB_Name = T2.Table_Name 
Left Join (select * from table( qsys2.QSQLIBL() ) T1) LIBL ON LIBL.SCHEMA_NAME = T2.TABLE_SCHEMA
ORDER BY LIBL.ORDINAL_POSITION, T2.TABLE_SCHEMA
;