How to use the merge command

Question

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.)

If I call a sproc called UpdateEmployee and I pass in a @Name, @Address, @Phone and @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?

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.)

Answer 1

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)

Answer 2

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:

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);