I am using the MERGE statement within a database project to populate reference data from a static value set, such as the following below:
MERGE INTO dbo.[User] AS TARGET
USING (VALUES
('me@somewhere.com', 'My Name'))
AS SOURCE(UserName, FullName)
ON SOURCE.UserName = TARGET.UserName
WHEN NOT MATCHED BY TARGET THEN
INSERT (UserId, UserName, FullName)
VALUES (NEWID(), UserName, FullName);
The problem comes in when I want to populate the secondary table based of content in other tables. For example, my UserPermission table contains user ID and role ID and I'd like my static value set to be something like ('me@somewhere.com', 'Admin') and be able to join to User and Permission to get the ID values for INSERTing. Not sure where do to that...
Edit:
User Table(ID, Username) 1, John Smith 2, Mark Wahlerg
Role Table(ID, RoleName) 1, Administrator 2, User 3, Guest
User-Role Table (User ID, Role ID)
I want the SQL for the MERGE statement to adjust the User-Role table such that I can do specify something like:
USING(VALUES
('John Smith', 'Administrator'),
('Mark Wahlburg', 'User')
and it will join to determine the IDs, insert the combinations that dont exists (and maybe delete the ones that do, but aren't in the MERGE.
Solution:
WITH CTE AS
(
SELECT UserId, RoleId
FROM (VALUES
('John Smith', 'Administrator'),
('Mark Wahlburg', 'User'))
AS SOURCE(UserName, RoleName)
INNER JOIN User ON SOURCE.UserName = User.UserName
INNER JOIN Role ON SOURCE.RoleName = Role.RoleName
)
MERGE INTO UserRole AS TARGET
USING CTE
ON CTE.UserId = TARGET.UserID AND CTE.RoleId = TARGET.UserId
WHEN NOT MATCHED BY TARGET THEN
INSERT(UserId, RoleId)
VALUES(UserId, RoleId)
