CODEWITHSUNDEEP
sqlsql-serverexists
marko
marko

Reputation: 517

sql server - checking existence for each row in insert

I need to do an insert and check for each row if the field "partita_iva" is already in this table, if yes the field "flag" must be 1, if not then must be 0. I did this, but it set 1 for all the rows, even if should be 1 just for one row

INSERT INTO tab2(id, flag)
select newid, 
CASE 
    WHEN EXISTS(SELECT * FROM tab1 WHERE partita_iva IN(SELECT partita_iva FROM tab2))
    THEN 1
    ELSE 0
END AS flag
from tab1

Upvotes: 1

Views: 102

Answers (3)

LukStorms
LukStorms

Reputation: 29657

It can probably be simplified.

INSERT INTO tab2 (id, flag)
SELECT t1.newid
, IIF(EXISTS(SELECT 1 FROM tab2 t2 WHERE t2.partita_iva = t1.partita_iva), 1, 0) AS flag
FROM tab1 t1

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Your current query logic seems correct to me, but you can correlate your sub-query : 

INSERT INTO tab2(id, flag)
      SELECT NEWID(), CASE WHEN EXISTS(SELECT 1 
                                       FROM tab2 t2 
                                       WHERE t2.partita_iva = t1.partita_iva 
                                      )
                           THEN 1 ELSE 0
                      END AS flag
      FROM tab1 t1;

NEWID() is a function that will creates a unique value of type uniqueidentifier.

Upvotes: 1

Thom A
Thom A

Reputation: 95557

As a total stab in the dark:

INSERT INTO tab2(Id,Flag)
SELECT newid, --Do you mean NEWID()?
       CASE WHEN EXISTS(SELECT 1 FROM tab2 T2 WHERE T2.partita_iva = T1.partita_iva) THEN 1 ELSE 0 END
FROM tab1 T1;

Upvotes: 0

Related Questions