upsert sql query
CREATE PROCEDURE [dbo].[MERGEStep1](@UserId nvarchar(50), @First_Name nvarchar(50), @Last_Name nvarchar(50), @TitlePosition nvarchar(30)) ASBEGINMERGE Company_Information WITH(HOLDLOCK) AS TUSING(SELECT 1 S) SON T.UserId = @UserIdWHEN MATCHED THEN UPDATE SET First_Name = @First_Name, Last_Name = @Last_Name, Title_Position=@TitlePosition WHEN NOT MATCHED THEN INSERT (UserId, First_Name, Last_Name, Title_Position) VALUES(@UserId, @First_Name,@Last_Name,@TitlePosition);END
Follow these steps:
- Create a variable to test it (ex: @id)
- Select @id = UserId from Company_Information where UserId = @UserId
- If @id = @userId update, otherwise insert
As @gbn specified, be aware of concurrence issues.