Reputation: 5249
i have 2 tables, one is called TEMP
and the other one is called MAIN
. So all what i am trying to do is to check if all records from the TEMP
table are in the MAIN
table. The logic should be all records from temp table must also be in the main table but when i run the sql query here; it does not give me any record and i know there are records missing in the main table from the temp table. what am i doing wrong here?
IF EXISTS(SELECT DISTINCT GRP_NM
,GRP_VAL
FROM TEMP
WHERE GRP_NM + GRP_VAL NOT IN (SELECT GRP_NM + GRP_VAL FROM MAIN)
)
BEGIN
INSERT INTO MAIN(GRP_NM, GRP_VAL )
SELECT GRP_NM
,GRP_VAL
FROM MAIN
WHERE GRP_NM + GRP_VAL NOT IN (SELECT GRP_NM + GRP_VAL FROM MAIN)
END
Upvotes: 0
Views: 169
Reputation: 153
In second part of your code you are trying to insert values from the same table MAIN (not from TEMP). Maybe just typo. Try like this:
IF EXISTS(SELECT DISTINCT GRP_NM, GRP_VAL FROM TEMP WHERE GRP_NM + GRP_VAL NOT IN (SELECT GRP_NM + GRP_VAL FROM MAIN))
BEGIN
INSERT INTO MAIN(GRP_NM, GRP_VAL )
SELECT GRP_NM, GRP_VAL FROM TEMP
WHERE GRP_NM + GRP_VAL NOT IN (SELECT GRP_NM + GRP_VAL FROM MAIN)
END
Upvotes: 1
Reputation: 40359
I suspect your problem has something to do with NULLs. If either GRP_NM or GRP_VAL is null in either table, then GRP_NM + GRP_VAL will be null, and your IN and EXISTS statements get totally bollixed up.
In any case, try this one out:
INSERT MAIN (GRP_NM, GRP_VAL)
select GRP_NM, GRP_VAL
from TEMP
except select GRP_NM, GRP_VAL
from MAIN
Upvotes: 2
Reputation: 69584
INSERT INTO MAIN(GRP_NM, GRP_VAL )
SELECT GRP_NM, GRP_VAL
FROM TEMP
WHERE NOT EXISTS (SELECT 1
FROM MAIN
WHERE GRP_NM = TEMP.GRP_NM
AND GRP_VAL = TEMP.GRP_VAL )
Upvotes: 2