I have a table named Usersettings where I want to execute this query
UPDATE US
SET MultiplayerEnabled = @MultiplayerEnabled,
Locale = @Locale
FROM KA_UserSettings US WITH(NOLOCK)
INNER JOIN @UserID U ON U.UserID=US.UserID
INSERT INTO KA_UserSettings (UserID, MultiplayerEnabled, Locale)
SELECT userid, @MultiplayerEnabled, @Locale FROM @UserID U
WHERE NOT EXISTS(SELECT 1 FROM KA_UserSettings US WITH (NOLOCK)
WHERE U.UserID=US.UserID)
I can replace this by using merge in higher version of sql server
MERGE dbo.KA_UserSettings AS A
USING(SELECT UserID,@MultiplayerEnabled,@Locale FROM @UserID )
AS B (UserID, MultiplayerEnabled, Locale)
ON (A.UserID=B.USerID)
WHEN MATCHED THEN UPDATE
SET A.MultiplayerEnabled = B.MultiplayerEnabled,A.Locale = B.Locale
WHEN NOT MATCHED THEN
INSERT(UserID, MultiplayerEnabled, Locale)
VALUES (B.UserID,B.MultiplayerEnabled,B.Locale)
OUTPUT $ACTION;