我可以在SQL Server 2005中使用MERGE语句吗?

问题描述

我正在使用SQL Server 2005，并且想在SQL Server 2005中的单个查询中创建MERGE语句或概念.

I am using SQL Server 2005 and I wanted to create MERGE statement or concept in single query in SQL Server 2005. Is it possible?

推荐答案

MERGE 是SQL Server 2008中引入的.如果要使用该语法，则需要升级.

MERGE was introduced in SQL Server 2008. If you want to use that syntax, you'll need to upgrade.

否则，典型方法将取决于源数据的来源.如果只有一行，并且您不知道是否需要更新或插入，则可能需要这样做:

Otherwise, the typical approach will depend on where the source data is from. If it's just one row and you don't know if you need to update or insert, you'd probably do:

UPDATE ... WHERE key = @key;

IF @@ROWCOUNT = 0
BEGIN
    INSERT ...
END

如果源是#temp表，表变量，TVP或其他表，则可以执行以下操作:

If your source is a #temp table, table variable, TVP or other table, you can do:

UPDATE dest SET ...
  FROM dbo.destination AS dest
  INNER JOIN dbo.source AS src
  ON dest.key = src.key;

INSERT dbo.destination SELECT ... FROM dbo.source AS src
  WHERE NOT EXISTS (SELECT 1 FROM dbo.destination WHERE key = src.key);

与MERGE一样(以及 Michael Swart在此处演示)，您将仍然想用适当的事务，错误处理和隔离级别将所有这些方法包围起来，使其表现得像真正的单一操作.甚至只有一个MERGE语句也不能保护您避免并发.

As with MERGE (and as Michael Swart demonstrated here), you will still want to surround any of these methods with proper transactions, error handling and isolation level to behave like a true, single operation. Even a single MERGE statement does not protect you from concurrency.

我已经在此处发布了有关MERGE的其他注意事项.

这篇关于我可以在SQL Server 2005中使用MERGE语句吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！