CODEWITHSUNDEEP
sqlsql-server
Maestro
Maestro

Reputation: 69

Insert if not exists else update then set flag on old records

Can someone suggest the best method or a Stored Proc that allows updating a directory.

Say we receive a new list of barristers for a chambers every 2 months. We need to

  1. Insert new records where they don't already exist - Based on First name, Surname
  2. If they exist then update certain fields that may be blank e.g. phone number
  3. Set any that weren't on the list to Live = 'N'

Generally getting lists in excel or an email, so having to clean up data.

select top 0 * into #temp from *table* 

Insert new data #temp

If not exists (select * from *table* where forename = 'X' and surname = 'Y' and ChambersID = 12) Insert into *table* (Title, forename, surname, yearofcall, ChambersID) values ('Mr','X', 'Y', 2018, 35) else update *table* set yearofcall = 2018 where forename = 'x' and surname = 'Y' and ChambersID = 12

This seems to work. Then i do another query. 

If not exists (select * from #temp where ChambersID = 12) update *table* set live = 'N' where ChambersID = 35

but long winded as have to build half query in excel... much prefer a stored proc where i can pass forename, surname and chambersid

Upvotes: 0

Views: 518

Answers (1)

Ankit Das
Ankit Das

Reputation: 650

Please try Merge in SQL. It suits your requirements. Here is a snippet

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;              --Instead of delete, set Live='N' in your case

Upvotes: 1

Related Questions