Given this setup:
DECLARE @Parts TABLE
(
PartId int NOT NULL,
MaskId int NOT NULL
);
INSERT INTO @Parts (PartId, MaskId)
VALUES
(100, 1000),
(200, 1000),
(300, 1000),
(400, 1500),
(500, 1700),
(600, 1700),
(700, 1700)
;
DECLARE @ChemicalMaster TABLE
(
ID int NOT NULL IDENTITY(1, 1),
ChemicalId int NOT NULL,
PartId int NOT NULL
);
INSERT INTO @ChemicalMaster (ChemicalId, PartId)
VALUES
(901, 100),
(920, 400),
(909, 700)
;
DECLARE @NewData TABLE
(
PartId int NOT NULL,
MaskId int NOT NULL,
ChemicalId int NOT NULL
);
INSERT INTO @NewData (PartId, MaskId, ChemicalId)
VALUES
(100, 1000, 901),
(700, 1700, 909),
(400, 1500, 920)
;
then the following query produces the expected results:
SELECT
N.ChemicalId,
P.PartId
FROM
@NewData As N
INNER JOIN @Parts As P
ON P.MaskId = N.MaskId
WHERE
Not Exists
(
SELECT 1
FROM @ChemicalMaster As M
WHERE M.ChemicalId = N.ChemicalId
And M.PartId = P.PartId
)
;
Output:
ChemicalId | PartId
-------------------
901 | 200
901 | 300
909 | 500
909 | 600
Stick an
INSERT INTO @ChemicalMaster (ChemicalId, PartId)
before the query, and the new rows will be inserted.