Search This Blog

Showing posts with label Merge statement. Show all posts
Showing posts with label Merge statement. Show all posts

Thursday, April 19, 2012

Using Merge to Update or Insert single row of data

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='gkindel@merge.com',
    --       @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