How to implement on duplicate update for SQL Server?
The MERGE statement, introduced in SQL Server 2008, would be the usual recommendation here. It is a feature that seems quite underused (in my case because until recently I've had to support SQL Server 2005) but pretty powerful.

See:
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql
https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx  

There are a number of issues to be careful of though with regard to concurrency (MERGE deadlocking itself(!)), performance, key consistency, and other bugs:
https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/73d2b0de-d6cc-4938-a303-b24d59c1be54/deadlock-on-simple-merge-statement?forum=sqlgetstarted
https://sqlperformance.com/2013/02/t-sql-queries/another-merge-bug  

The alternative is to use a multi-step process (delete what is no longer need, update what is already present, insert what is missing) wrapped in a transaction with appropriate settings.