WITH RECURSIVE ordinates AS (
	SELECT
		mao.OrdinateID,
		mao.ParentOrdinateID
	FROM
		mAxisOrdinate mao
UNION ALL
	SELECT
		o.OrdinateID,
		mao.ParentOrdinateID
	FROM
		ordinates o
	JOIN mAxisOrdinate mao ON
		o.ParentOrdinateID = mao.OrdinateID
),
ordinatesTree AS (
	SELECT
		*
	FROM
		(
			SELECT
				DISTINCT o.OrdinateID,
				o.OrdinateID AS ParentOrdinateID
			FROM
				ordinates o
		UNION
			SELECT
				*
			FROM
				ordinates o
			WHERE
				o.ParentOrdinateID IS NOT NULL
		)
	ORDER BY
		OrdinateID ASC,
		ParentOrdinateID DESC
),
ordinatesCategorisations AS (
	SELECT
		ot.OrdinateID,
		ot.ParentOrdinateID,
		moc.OrdinateID AS SourceOrdinateID,
		moc.DimensionID,
		moc.MemberID ,
		moc.DimensionMemberSignature ,
		moc."Source" ,
		moc.DPS
	FROM
		ordinatesTree ot
	JOIN mOrdinateCategorisation moc ON
		ot.ParentOrdinateID = moc.OrdinateID
),
categorisationsToBeInserted AS (
	SELECT
		oc.OrdinateID,
		oc.DimensionID,
		oc.MemberID,
		oc.DimensionMemberSignature,
		oc."Source",
		oc.DPS
	FROM
		ordinatesCategorisations oc
	LEFT JOIN mOrdinateCategorisation moc ON
		oc.OrdinateID = moc.OrdinateID
		AND oc.DimensionID = moc.DimensionID
	WHERE
		moc.OrdinateID IS NULL
)
INSERT
	OR IGNORE
INTO
	mOrdinateCategorisation
SELECT
	*
FROM
	categorisationsToBeInserted;