CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

issues using if exists statement in sql

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

Answers (3)

AndLev
AndLev

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

Philip Kelley
Philip Kelley

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

M.Ali
M.Ali

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

Related Questions