CODEWITHSUNDEEP
sql-servert-sql
sergiom
sergiom

Reputation: 4877

Merge statement on a single record table

I need to write a single statement to insert or update a record in a single record table

the merge statement allows me to write this:

create table t1 (n int)

-- insert into t1 (n) Values (1);  -- uncomment to test the matched branch

MERGE t1 AS P
USING (SELECT 3 AS n) AS S
ON 1 = 1
WHEN MATCHED THEN
    UPDATE SET n = S.n
WHEN NOT MATCHED THEN
    INSERT (n) 
    VALUES (S.n);

select * from t1

this work, but I think that the 1=1 condition purpose is not very easy to understand. Is there a different syntax to insert a record when the table is empty or update the record when it does already exist?

Upvotes: 1

Views: 2970

Answers (3)

Gary Kindel
Gary Kindel

Reputation: 17699

Example of recent procedure I wrote to either update an existing row or insert a new row. Table has the same structure as MembershipEmailFormat the table variable.

Found it easiest to create a table variable to be the source in the Using clause. I realize that the main purpose of Merge statements really are merging muliple rows between two tables. My use case is that I need to insert a new email address for a user or modify and existing email address. 

CREATE PROCEDURE [dbo].[usp_user_merge_emailformat]
        @UserID UNIQUEIDENTIFIER,
        @Email varchar(256),
        @UseHTML bit
AS
BEGIN
    --SELECT @UserID='04EFF187-AEAC-408E-9FA8-284B31890FBD',
    --       @Email='[email protected]',
    --       @UseHTML=0

    DECLARE @temp TABLE
    (
            UserID UNIQUEIDENTIFIER,
            Email varchar(256),
            HtmlFormat bit
    )       

    INSERT INTO @temp(UserID,Email, HtmlFormat)
    Values(@UserID,@Email,@UseHTML)

    SELECT * FROM @temp    

    MERGE dbo.MembershipEmailFormat as t
    USING @temp AS s
    ON (t.UserID = s.UserID and t.Email = s.Email)
    WHEN MATCHED THEN UPDATE SET t.HtmlFormat = s.HtmlFormat
    WHEN NOT MATCHED THEN INSERT VALUES(s.UserID,s.Email,s.HtmlFormat);
END

Upvotes: 0

brian
brian

Reputation: 3695

Replace

ON 1 = 1

with

ON S.n = P.n

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

The other option would be to do it the old fashioned way.

if exists (select null from t1)
    update t1 set n = 3
else
    insert into t1 (n) values (3)

Upvotes: 2

Related Questions