CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

How to use if not exists in sql

I am trying to use if exists statement but having some issues. I am checking if LOCATION and NAME exist in my NEWTABLE, if they don't exist then i want to insert them into my NewTable. So basically i am trying to find Location and Names where records exist in the OldTable but not in the newTable. thanks
Here is my sql

INSERT INTO NewTable(Location, Name)
            SELECT Location, Name
            FROM OldTable
            WHERE Location and Name NOT IN (select Location, Name from NewTable)

Upvotes: 2

Views: 138

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You would use not exists for this:

INSERT INTO NewTable(Location, Name)
        SELECT Location, Name
        FROM OldTable ot
        WHERE NOT EXISTS (select 1
                          from NewTable nt 
                          where nt.LOCATION = ot.LOCATION and nt.NAME = ot.NAME
                         );

You can also enforce this constraint by building a unique index:

create unique index NewTable_Location_Name on NewTable(Location, Name);

Note that this would cause an error when duplicates are encountered (without the additional check).

Upvotes: 6

Related Questions