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
No comments:
Post a Comment