1

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.

  • 3
    I do not know about libraries, but SQL server support MERGE, there are many threads there, like - stackoverflow.com/questions/18873747/… – a_vlad Apr 28 '17 at 8:31
  • @a_vlad Thank you, MERGE works as expected. But did not found any api provided by SQL Server to perform merge operation though C program. As per MSDN SQLBulkOperation() used for read/update/delete/insert data in bulk – Pravin Apr 28 '17 at 10:31
  • 1
    @Pravin: why do you need a special "API"? Just run the statement – a_horse_with_no_name Apr 28 '17 at 11:27
2

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.

  • Merge will solve update on duplicate problem. But SQL server didn't provide ODBC interface perform merge operation. Alternative process will be helpful but need to verify for bulk in operation. Thanks – Pravin Apr 28 '17 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.