SQL Merge statement not working in Stored Procedure
10,362
Your Source shouldn't rely upon the target table. Try instead:
MERGE [Users].[Addresses] AS Target
USING (select @UserID,@Address1,@Address2,@Town,@County,@PostCode,@Country,@Type)
AS Source (UserID,Address1,Address2,Town,County,PostCode,Country,Type)
ON (Source.UserId = Target.UserId)
WHEN MATCHED THEN
UPDATE SET Target.Address1 = Source.Address1,
Target.Address2 = Source.Address2,
Target.Town = Source.Town,
Target.County = Source.County,
Target.Postcode = Source.Postcode,
Target.Country = Source.Country
WHEN NOT MATCHED BY TARGET THEN
INSERT ([UserId], [Address1], [Address2], [Town], [County], [PostCode], [Country], [Modified], [Type])
VALUES(Source.UserId, Source.Address1, Source.Address2, Source.Town, Source.County, Source.PostCode, Source.Country, GetDate(), Source.Type);
At the moment, you're creating a zero-row Source
rowset, so of course nothing happens in the merge.
Author by
ChrisBint
Updated on June 07, 2022Comments
-
ChrisBint about 2 years
The following code does not seem to work. If the address does not exist, it does not insert the new record. However, if the address does exist, it does get updated.
ALTER PROCEDURE [Users].[UpdateAddress] @UserId int, @Address1 varchar(100), @Address2 varchar(100), @Town varchar(100), @County varchar(50), @PostCode varchar(50), @Country varchar(50), @Type INT AS MERGE [Users].[Addresses] AS Target USING (SELECT UserId FROM [Users].[Addresses] WHERE UserId = @UserId) AS Source ON (Source.UserId = Target.UserId) WHEN MATCHED THEN UPDATE SET Target.Address1 = @Address1, Target.Address2 = @Address2, Target.Town = @Town, Target.County = @County, Target.Postcode = @Postcode, Target.Country = @Country WHEN NOT MATCHED BY TARGET THEN INSERT ([UserId], [Address1], [Address2], [Town], [County], [PostCode], [Country], [Modified], [Type]) VALUES(@UserId, @Address1, @Address2, @Town, @County, @PostCode, @Country, GetDate(), @Type);