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);
Advertisement
Answer
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 )