SQL MERGE statement to update data

by

Assuming you want an actual SQL Server MERGE statement:

MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target
USING dbo.temp_energydata AS source
    ON target.webmeterID = source.webmeterID
    AND target.DateTime = source.DateTime
WHEN MATCHED THEN 
    UPDATE SET target.kWh = source.kWh
WHEN NOT MATCHED BY TARGET THEN
    INSERT (webmeterID, DateTime, kWh)
    VALUES (source.webmeterID, source.DateTime, source.kWh);

If you also want to delete records in the target that aren’t in the source:

MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target
USING dbo.temp_energydata AS source
    ON target.webmeterID = source.webmeterID
    AND target.DateTime = source.DateTime
WHEN MATCHED THEN 
    UPDATE SET target.kWh = source.kWh
WHEN NOT MATCHED BY TARGET THEN
    INSERT (webmeterID, DateTime, kWh)
    VALUES (source.webmeterID, source.DateTime, source.kWh)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Because this has become a bit more popular, I feel like I should expand this answer a bit with some caveats to be aware of.

First, there are several blogs which report concurrency issues with the MERGE statement in older versions of SQL Server. I do not know if this issue has ever been addressed in later editions. Either way, this can largely be worked around by specifying the HOLDLOCK or SERIALIZABLE lock hint:

MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target
[...]

You can also accomplish the same thing with more restrictive transaction isolation levels.

There are several other known issues with MERGE. (Note that since Microsoft nuked Connect and didn’t link issues in the old system to issues in the new system, these older issues are hard to track down. Thanks, Microsoft!) From what I can tell, most of them are not common problems or can be worked around with the same locking hints as above, but I haven’t tested them.

As it is, even though I’ve never had any problems with the MERGE statement myself, I always use the WITH (HOLDLOCK) hint now, and I prefer to use the statement only in the most straightforward of cases.

More Related Contents:

  1. T-SQL: Export to new Excel file
  2. How to get the last record per group in SQL
  3. T-SQL substring – separating first and last name
  4. How to get week number of the month from the date in sql server 2008
  5. T-SQL strip all non-alpha and non-numeric characters
  6. Avoiding IF ELSE due to variable been NULL
  7. Recursive Child/Parent queries in T/SQL
  8. Is it possible to write single query on two table which are not connected to each other?
  9. Comma separated results in SQL
  10. Understanding PIVOT function in T-SQL
  11. Using RegEx in SQL Server
  12. Is the NOLOCK (Sql Server hint) bad practice?
  13. Where value in column containing comma delimited values
  14. Parse comma-separated string to make IN List of strings in the Where clause
  15. SQL Server 2008 Empty String vs. Space
  16. Sql Server ‘Saving changes is not permitted’ error ► Prevent saving changes that require table re-creation
  17. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  18. Procedure or function !!! has too many arguments specified
  19. How to restore to a different database in SQL Server?
  20. SQL “between” not inclusive
  21. Why is SQL Server 2008 Management Studio Intellisense not working?
  22. How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table
  23. Can I set ignore_dup_key on for a primary key?
  24. Using PIVOT in SQL Server 2008
  25. Do clustered indexes have to be unique?
  26. What is the default transaction isolation level for SQL Server with ADO.NET?
  27. When using GETDATE() in many places, is it better to use a variable?
  28. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  29. Execute a SQL Stored Procedure and process the results [closed]
  30. What are row, page and table locks? And when they are acquired?

Leave a Comment