Geek Answers Handbook

How to use the merge command

Let's say I have a table with the name Employee(it has columns ID, NAME, ADDRESSand PHONE). (Not my real problem, but simplified to simplify the issue.)

If I call sproc called UpdateEmployee, and I pass the values @Name, @Address, @Phoneand @ID.

Is it possible to merge to easily check if an identifier exists? If it is necessary to update the name, address and phone? and if he does not insert them?

I see examples on the net, but they are huge and hairy. I would like to have a good simple example.

(We recently upgraded to SQL 2008, so I'm new to the merge team.)

+5
source share
2

. . . :

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

:

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

, docs :

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)
+5

More articles:


All Articles