How to write query insert related parts for maskid into chemical master table if not exist ?

Question

0.00/5 (No votes)

See more:

How to write query Insert related parts for maskId into chemical master table if not exist on table chemical Master ?

SQL

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 8-Jan-20 14:27pm

ahmed_sa

Updated 9-Jan-20 8:30am

Add a Solution

Comments

ZurdoDev 9-Jan-20 7:47am

IF NOT EXISTS (SELECT something ...)
BEGIN
-- do something
END

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2020-01-09T08:31:00

Given this setup:

SQL

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:

SQL

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:

plain

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.