如何使用merge命令

Say I have a table called Employee (has ID, NAME, ADDRESS, and PHONE columns). (Not my real problem, but simplified to make the question easier.)

假设我有一个名为Employee的表（具有ID，NAME，ADDRESS和PHONE列）。（不是我真正的问题，而是简化以使问题更容易。）

If I call a sproc called UpdateEmployee and I pass in a @Name, @Address, @Phone and @ID.

如果我调用名为UpdateEmployee的sproc并传入@ Name，@ Address，@ Phone和@ID。

Can merge be used to easily check to see if the ID exists? If it does to update the name, address and phone? and if it does not to insert them?

可以使用合并来轻松检查ID是否存在？如果它确实更新了名称，地址和电话？如果不插入它们？

I see examples on the net, but they are huge and hairy. I would like a nice simple example if possible.

我在网上看到了一些例子，但它们很庞大而且毛茸茸。如果可能的话，我想要一个很好的简单例子。

(We recently upgraded to SQL 2008, so I am new to the merge command.)

（我们最近升级到SQL 2008，所以我是merge命令的新手。）

2 个解决方案

#1

Bill Karwin's code is almost correct. I made the needed changes. Playing with the variable values will allow you to see it in action. Table:

Bill Karwin的代码几乎是正确的。我做了必要的改变。使用变量值可以让您看到它的实际效果。表：

CREATE TABLE [dbo].[employee](
    [ID] [int] NULL,
    [Name] [char](20) NULL,
    [Address] [char](20) NULL,
    [Phone] [int] NULL
) ON [PRIMARY]

Code:

码：

DECLARE @ID int, @NAME char(20), @ADDRESS char(20), @PHONE int
SET @ID=2
SET @NAME='Jenny'
SET @ADDRESS='11 My St'
SET @PHONE=228326

MERGE Employee AS target
USING (SELECT @ID, @NAME, @ADDRESS, @PHONE) AS source (ID, Name, Address, Phone)
ON (target.ID = source.ID)
WHEN MATCHED THEN
  UPDATE SET NAME    = source.Name,
             ADDRESS = source.Address,
             PHONE   = source.Phone
WHEN NOT MATCHED THEN
  INSERT (ID, NAME, ADDRESS, PHONE) 
  VALUES (source.ID, source.Name, source.Address, source.Phone);

#2

I have not tested this, but based on the docs this may get you on the right track:

我没有对此进行测试，但根据文档，这可能会让您走上正轨：

MERGE myschema.Employee AS target
USING (SELECT @ID, @NAME, @ADDRESS, @PHONE) AS source (ID, Name, Address, Phone)
ON (target.ID = source.ID)
WHEN MATCHED THEN
  UPDATE SET NAME    = source.Name
             ADDRESS = source.Address
             PHONE   = source.Phone
WHEN NOT MATCHED THEN
  INSERT (ID, NAME, ADDRESS, PHONE) 
  VALUES (source.ID, source.Name, source.Address, source.Phone)

#1