issues using if exists statement in sql

Question

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

What happens when you just run the SELECT GRP_NM, GRP_VAL FROM MAIN WHERE GRP_NM + GRP_VAL NOT IN (SELECT GRP_NM + GRP_VAL FROM MAIN) part alone? You appear to be repeating the same logic too, I'm not sure you need the outer if exists at all. — John Powell
– John Powell, Commented May 21, 2014 at 17:58
it returns no value and i know there are almost about 28 records that are missing — moe
– moe, Commented May 21, 2014 at 18:00

Philip Kelley · Accepted Answer · 2014-05-21 18:05:21Z

2

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

answered May 21, 2014 at 18:05

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M.Ali · Accepted Answer · 2014-05-21 18:05:12Z

2

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 )

answered May 21, 2014 at 18:05

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Comments

AndLev · Accepted Answer · 2014-05-21 18:06:04Z

1

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

answered May 21, 2014 at 18:06

AndLev

1537 bronze badges

1 Comment

Bogdan Sahlean Over a year ago

The comparison of concatenated values isn't a good idea: SELECT CASE WHEN 'A' + '1' = 'A ' + '1' THEN 'TRUE' ELSE 'FALSE' END.

Collectives™ on Stack Overflow

issues using if exists statement in sql

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related