I'm trying insert/update data for table T1(int id,char name, float data), which has unique a index on id and name.
I'm using SQLBulkOperation(SQL_ADD) to add records to the table, and SQLBulkOperation(SQL_UPDATE_BY_BOOKMARK) to update them.
Now my use case is that I have to update records if they already exist and insert if not. For this, MySQL provides INSERT INTO ... ON DUPLICATE KEY UPDATE.
How to implement the same in SQL Server? Is there any other efficient way to do this?
I'm using libmsodbcsql-13.0.so.0.0 to connect to SQL Server.
Best Answer
The
MERGEstatement, 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 (
MERGEdeadlocking 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.