Insert differences between 2 tables into 3rd table-CodePudding

I have total of 3 tables in my database.

My Testing1 and Testing2 table have two exact columns (ItemID and ItemPath). My Testing3 have three columns (ItemID, ItemPath and Source).

I'm trying to compare Testing1 with Testing2 and insert the difference to Testing3.

I'm able get the the difference but I'm just wondering how can modify it to meet this scenario.

For Example.

If Item is only existed in Testing1 then insert it into Testing3 table with "Testing1" as Source column value.

If Item is only existed in Testing2 then insert it into Testing3 table with "Testing2" as Source column value.

INSERT INTO Testing3 (ItemId, ItemPath, Source)
SELECT pm.ItemID, pm.ItemPath
FROM Testing1 pm
WHERE NOT EXISTS(SELECT ec.ItemId
FROM Testing2 ec
WHERE ec.ItemID = pm.ItemID);

CodePudding user response：

Use UNION ALL to combine your results for insert. And add a static value for Source.

INSERT INTO Testing3 (ItemId, ItemPath, Source)
SELECT pm.ItemID, pm.ItemPath, 'Testing1'
FROM Testing1 pm
WHERE NOT EXISTS (
    SELECT ec.ItemId
    FROM Testing2 ec
    WHERE ec.ItemID = pm.ItemID
)
UNION ALL
SELECT pm.ItemID, pm.ItemPath, 'Testing2'
FROM Testing2 pm
WHERE NOT EXISTS (
    SELECT ec.ItemId
    FROM Testing1 ec
    WHERE ec.ItemID = pm.ItemID
)