MERGE käsuga puuduvate ridade lisamine tabelmuutujasse

On selline tabelmuutuja
DECLARE @tulem TABLE ( WORKER NVARCHAR(40) COLLATE DATABASE_DEFAULT, HOUSES_GROUPS_ID INT, TOTAL INT DEFAULT 0) ja esialgne päring annab tabeli sisuks

aga vaja näidata ka puuduolevaid 0 ridu, ehk saada selline tulemus

ehk lisame KAMPUS_OLLE-le puudu oleva HOUSES_GROUPS_ID 42 korral koguse 0

MERGE lause võib olla selline:


MERGE @tulem AS Target
    USING ( SELECT DISTINCT WK.WORKER, HG.HOUSES_GROUPS_ID FROM @tulem WK CROSS APPLY ( SELECT DISTINCT HOUSES_GROUPS_ID    FROM @tulem ) AS HG
       ) AS Source (WORKER, HOUSES_GROUPS_ID) ON (Target.HOUSES_GROUPS_ID = Source.HOUSES_GROUPS_ID AND Target.WORKER = Source.WORKER)
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (HOUSES_GROUPS_ID, WORKER) VALUES (Source.HOUSES_GROUPS_ID, Source.WORKER);


Sisemine päring MERGE lauses:
SELECT DISTINCT WK.WORKER, HG.HOUSES_GROUPS_ID FROM @tulem WK CROSS APPLY ( SELECT DISTINCT HOUSES_GROUPS_ID FROM @tulem ) AS HG

annab WORKER ja HOUSES_GROUPS_ID ristkorrutise

ja sealt mestib WHEN NOT MATCHED puuduolevad lisaks algsesse tabelisse.
Kuna @tulem TOTAL INT DEFAULT 0 vaikeväärtus on 0 pannakse see ka vaikimisi puuduvatele väärtuseks