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

Question

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

Insert new records where they don't already exist - Based on First name, Surname
If they exist then update certain fields that may be blank e.g. phone number
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

Have you had a look at `MERGE`? If not, what *have* you tried? — Thom A, Nov 21 '19 at 10:51
Does this answer your question? [Solutions for INSERT OR UPDATE on SQL Server](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — Fourat, Nov 21 '19 at 10:59

score 1 · Accepted Answer · answered Nov 21 '19 at 10:52

1

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

answered Nov 21 '19 at 10:52

Ankit Das

640
4
16

This method suits me best. I will try to create some variables and then a stored proc. – Maestro Nov 21 '19 at 11:57

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

1 Answers1