SQL Server MERGE + Verknüpfen anderer Tabellen
Ich benutze die MERGE-Anweisung in einem Datenbankprojekt, um Referenzdaten aus einem statischen Wertesatz zu füllen, wie im Folgenden dargestellt:
<code> MERGE INTO dbo.[User] AS TARGET USING (VALUES ('[email protected]', '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); </code>
Das Problem tritt auf, wenn ich die sekundäre Tabelle basierend auf dem Inhalt in anderen Tabellen auffüllen möchte. Beispielsweise enthält meine UserPermission-Tabelle die Benutzer-ID und die Rollen-ID, und ich möchte, dass mein statischer Wert wie folgt festgelegt wird ('[email protected]', 'Admin') und mit User and Permission verknüpft wird, um die zu erhalten ID-Werte für INSERTING. Ich weiß nicht, wo ich das anstellen soll ...
Bearbeiten:
Benutzertabelle (ID, Benutzername) 1, John Smith 2, Mark Wahlerg
Rollentabelle (ID, Rollenname) 1, Administrator 2, Benutzer 3, Gast
Benutzerrollentabelle (Benutzer-ID, Rollen-ID)
Ich möchte, dass die SQL für die MERGE-Anweisung die User-Role-Tabelle so anpasst, dass ich Folgendes angeben kann:
<code>USING(VALUES ('John Smith', 'Administrator'), ('Mark Wahlburg', 'User') </code>
und es wird sich verbinden, um die IDs zu bestimmen, die nicht existierenden Kombinationen einzufügen (und möglicherweise diejenigen zu löschen, die dies tun, aber nicht im MERGE sind.
Lösung:
<code>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) </code>