SQL MERGE statement to update data

Question

I've got a table with data named energydata

it has just three columns

(webmeterID, DateTime, kWh)

I have a new set of updated data in a table temp_energydata.

The DateTime and the webmeterID stay the same. But the kWh values need updating from temp_energydata table.

How do I write the T-SQL for this the correct way?

Solution 1

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.

Solution 2

I often used Bacon Bits great answer as I just can not memorize the syntax.

But I usually add a CTE as an addition to make the DELETE part more useful because very often you will want to apply the merge only to a part of the target table.

WITH target as (
    SELECT * FROM dbo.energydate WHERE DateTime > GETDATE()
)
MERGE INTO target WITH (HOLDLOCK)
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

Solution 3

If you need just update your records in energydata based on data in temp_energydata, assuming that temp_enerydata doesn't contain any new records, then try this:

UPDATE e SET e.kWh = t.kWh
  FROM energydata e INNER JOIN 
       temp_energydata t ON e.webmeterID = t.webmeterID AND 
                            e.DateTime = t.DateTime

Here is working sqlfiddle

But if temp_energydata contains new records and you need to insert it to energydata preferably with one statement then you should definitely go with the answer that Bacon Bits gave.

Comments

[Andriy M]

The NOT MATCHED BY SOURCE clause may need to be used with caution in this case. If temp_energydata contains updates for only a subset of members in energydata, your second MERGE is going to delete the data of all members not found in the temporary set.

[Bacon Bits]

@AndriyM Which is why I said "If you also want to delete records in the target that aren't in the source". I'm not sure how this would be confusing?

[Andriy M]

Well, perhaps not confusing but, to an inexperienced person, it might not be entirely obvious that, when they want to use the temp set to update a subset of rows (in particular, a subset of members) in the main table, the rows deleted will also include those members that weren't supposed to be updated. I'm not insisting (that it may not be obvious), though, as I might as well just be overcautious there, so please disregard my comment if you think so.

[peterm]

That will most likely overwrite meter readings in energydata for dates other than those in temp_energydata, which might by surprising and undesired outcome.

[peterm]

That will most likely overwrite meter readings in energydata for dates other than those in temp_energydata, which might by surprising and undesired outcome.

[user1745767]

Perfect solution for me. My temp data did contain new records as well. I didn't need to delete records in the target that were not in the source.

[Andrew Steitz]

Not if there are NEW records in temp_energydata. Sure, you could add a INSERT INTO ... SELECT * FROM ... old LEFT JOIN new WHERE old.foo IS NULL (before or after the UPDATE) but it is two statements and if there is enough data the execution time could be long enough to cause issues unless you LOCK the table and if you do that your are likely to infuriate users (not enough space here to go into all the scenarios). All that said, I PREFER the UPDATE then INSERT (or vice versa) myself but it does not answer the OP's question.

[Ali]

Is there a semicolon missing at the end of the merge statement?

[Ali]

Thanks @BaconBits, I was getting a few red squigglies. Intellisense doesn't seem to be so robust around MERGE queries.

[Bacon Bits]

@Ali Oh, no, you're right. MERGE statements explicitly need to be terminated with semicolons! The query analyzer I was using just added them automatically.

[Ali]

@BaconBits Ah, OK. Thanks for following up. Good to know.

[Henrik Staun Poulsen]

you can also enhance your USING clause to a full SELECT statement. This works fine if the query is simple, but I've seen very bad execution plans if the query had more than 1-2 tables. In this case, I would use a #temp table or a CTE, as per your example

[bsplosion]

This more directly answers the actual question, which looks like an XY problem - it doesn't seem that they were literally asking for a MERGE operation, but rather how to merge data from one table into another (which is just an UPDATE as shown here).