I have a table UserRole:

|User|Role|
|----|----|
|A   |  X |
|B   |  Y |
|C   |  Z |
|D   |  W |

Now a new table comes in say UserRoleNew:

|User|Role|
|----|----|
|A   |   X|
|A   |   W|
|B   |   Z|
|C   |   Z|
|F   |   X|

Based on UserRoleNew I need to modify UserRole table following these rules:

-> If the role for a user changed then the existing role should be deleted and new role should be inserted.

-> If there is a new user then the user and role should be inserted

-> If there is no information about an existing user in the UserRoleNew then this user and his role should be retained.

-> One user can have multiple roles

So the new UserRole table should look like this:

|User|Role|
|----|----|
|A   |   X|
|A   |   W|
|B   |   Z|
|C   |   Z|
|D   |   W|
|F   |   X|

Edit-> Table definition of UserRole is:

CREATE TABLE [dbo].[UserRole]( 
    [Id] [int] NOT NULL CONSTRAINT [Pk_UserRole] PRIMARY KEY IDENTITY,  
    [ApplicationUserId] [int] NOT NULL, 
    [UserRoleId] [int] NOT NULL
)

CodePudding user response：

You can use MERGE for this.

But the missing users need to be included in the source query.
Since the WHEN NOT MATCHED BY SOURCE THEN DELETE is kinda brutal.

--
-- Merge UserRoleNew into UserRole, including the missing users
--
MERGE INTO UserRole tgt
USING (
  SELECT ApplicationUserId, UserRoleId
  FROM UserRoleNew
  
  UNION ALL
  
  SELECT ApplicationUserId, UserRoleId
  FROM UserRole t
  WHERE NOT EXISTS (
    SELECT 1
    FROM UserRoleNew t2
    WHERE t2.ApplicationUserId = t.ApplicationUserId
  )
) src 
  ON (src.ApplicationUserId = tgt.ApplicationUserId
 AND src.UserRoleId = tgt.UserRoleId)
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ApplicationUserId, UserRoleId) 
    VALUES (src.ApplicationUserId, src.UserRoleId)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
GO

4 rows affected

SELECT ApplicationUserName, UserRoleName
FROM UserRole AS ur
LEFT JOIN ApplicationUsers AS usr
  ON usr.ApplicationUserId = ur.ApplicationUserId
LEFT JOIN UserRoleNames role 
  ON role.UserRoleId = ur.UserRoleId
ORDER BY ApplicationUserName, UserRoleName;
GO

ApplicationUserName | UserRoleName
:------------------ | :-----------
A                   | W           
A                   | X           
B                   | Z           
C                   | Z           
D                   | W           
F                   | X           

db<>fiddle here