Click here to Skip to main content
14,427,400 members
Rate this:
Please Sign up or sign in to vote.
How to write query Insert related parts for maskId into chemical master table if not exist on table chemical Master ? 


create table #temp
(
partid     int,
maskid     int,
chemicalid int,
status  nvarchar(50)
)
insert into #temp
values
(100,1000,901)
(700,1700,909)
(400,1500,920)
Parts   
pk       
PartId    maskId
100       1000
200       1000
300       1000
400       1500
500       1700
600       1700
700       1700

Chemical Master Table
   PK                          fk
Chemicalmasterid  ChemicalId  PartId
  1                901         100
  4                920         400
  7                909         700


How to write select statement return related parts to maskId on temp table #temp from table Parts
then insert into table checmicalmaster related parts to maskId on temp table #temp in case of not exist on table chemical master

as Explain

maskId 1000 have 100 and 200 and 300

then i will check on table chemical master to related parts for maskId not added

I found that 200 and 300 related to MaskId and not exist on table chemicalMaster

then add them to table chemicalmaster with chemicalId to last value for mask on table

ChemicalMatser

What I have tried:

values must be inserted into chemical master table will be :
FinalResult 
8   901 200  
9   901 300 
10  909 500
11  909 600
Posted
Updated 9-Jan-20 9:30am
Comments
ZurdoDev 9-Jan-20 7:47am
   
IF NOT EXISTS (SELECT something ...)
BEGIN
-- do something
END

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 5,128
Richard MacCutchan 1,804
phil.o 1,270
Patrice T 1,217
MadMyche 1,110



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100