用于更新数据的 SQL MERGE 语句 [英] SQL MERGE statement to update data

问题描述

我有一个名为 energydata

它只有三列

(webmeterID, DateTime, kWh)

我在表 temp_energydata 中有一组新的更新数据.

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

DateTime 和 webmeterID 保持不变.但是 kWh 值需要从 temp_energydata 表中更新.

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

我如何以正确的方式为此编写 T-SQL?

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

推荐答案

假设您想要一个实际的 SQL Server MERGE 语句:

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.

首先，有几个博客报告 与旧版 SQL Server 中 MERGE 语句 的并发问题.我不知道这个问题是否在以后的版本中得到了解决.无论哪种方式，这都可以通过指定 HOLDLOCK 或 SERIALIZABLE 锁定提示来解决:

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.

还有其他几个已知问题 与 MERGE.(请注意，由于 Microsoft 取消了 Connect 并且没有将旧系统中的问题与新系统中的问题联系起来，因此这些旧问题很难追踪.谢谢，Microsoft！)据我所知，其中大多数并不常见问题或可以使用与上述相同的锁定提示来解决，但我尚未对其进行测试.

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.

实际上，即使我自己从未遇到过 MERGE 语句的任何问题，但我现在总是使用 WITH (HOLDLOCK) 提示，而且我更喜欢仅在最直接的情况下使用该语句.

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.

这篇关于用于更新数据的 SQL MERGE 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！