One more question. If I reenable the unique on stock_code would the
correct statement look like this to achieve the same thing?

INSERT INTO TBL_INVENTORY(
INVENTORY_ID,
STOCK_CODE,
DESCRIPTION,
COST_PRICE,
SELLING_PRICE,
INVENTORY_TYPE)
SELECT
tgic.INVENTORY_ID,
tgic.STOCK_CODE || ' (Deleted)',
tgic.DESCRIPTION || ' (Deleted)',
0,
0,
tgic.INVENTORY_TYPE

FROM TBL_JOB_CARD_INVENTORY tgic
where not exists (
select 1 from TBL_INVENTORY ti
where ti.INVENTORY_ID = tgic.INVENTORY_ID
OR ti.STOCK_CODE = tgic.STOCK_CODE )