Friday 12 September 2014

SQL Server 2012 Merge with list

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;