我可以在 SQL Server 2005 中使用 MERGE 语句吗? [英] Can I use the MERGE statement in SQL Server 2005?

问题描述

我使用的是 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 的其他注意事项.

I've published some other cautions about MERGE in more detail here.

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