Month: February 2023

To sync a materialized view

To manually keep a results cache updated I recommend concatenating multi column primary keys rather than using full joins. If it looks simple it should last.

/* 1 Insert */

INSERT INTO 
	[mv].[sometablename]
SELECT 
	*
FROM 
	[vw].[vw_someviewname]
WHERE
	[pkcomposite1] + [pkcomposite2]
NOT IN
	(
	SELECT
		[pkcomposite1] + [pkcomposite2]
	FROM
		[mv].[sometablename]
	);


/* 2 Delete */

DELETE FROM
	[mv].[sometablename]
WHERE
	[pkcomposite1] + [pkcomposite2]
NOT IN
	(
	SELECT
		[pkcomposite1] + [pkcomposite2]
	FROM
		[vw].[vw_someviewname]
	);


/* 3 Update */

UPDATE
	MAT
SET
	MAT.[somecolumn1] = VW.[somecolumn1],
	MAT.[somecolumn2] = VW.[somecolumn2]
FROM
	[mv].[sometablename] MAT
JOIN
	[vw].[vw_someviewname] VW
ON
	MAT.[pkcomposite1] + MAT.[pkcomposite2]
	=
	VW.[pkcomposite1] + VW.[pkcomposite2]
WHERE
	MAT.[somecolumn1] <> VW.[somecolumn1]
OR
	MAT.[somecolumn1] <> VW.[somecolumn2];