SQL_Kurs

T‑SQL MERGE · Das Synchronisations‑Wunder

Einfügen, Aktualisieren, Löschen in einem Durchlauf – Praktische Beispiele mit Record Counting

🔰 Basis 🔄 UPSERT (nur Update/Insert) ⚡ Vollsync mit Delete 🧩 Quelle als CTE/Subquery 📊 Record Count (OUTPUT + $action)

🔰 1. Einfaches Beispiel – UPSERT (INSERT / UPDATE ohne DELETE)

📌 Ziel: Synchronisieren einer Zieltabelle mit einer Quelle. Existiert der Datensatz bereits (MATCHED), wird er aktualisiert, ansonsten eingefügt (NOT MATCHED). Löschungen werden ignoriert – das klassische "UPSERT".

-- Quelltabelle (z.B. Staging) und Zieltabelle
CREATE TABLE #SourceProducts (ProductID INT, ProductName VARCHAR(50), Price DECIMAL(10,2));
CREATE TABLE #TargetProducts (ProductID INT PRIMARY KEY, ProductName VARCHAR(50), Price DECIMAL(10,2));

INSERT INTO #SourceProducts VALUES (1, 'Laptop', 999.99), (2, 'Maus', 19.99), (3, 'Tastatur', 49.99);
INSERT INTO #TargetProducts VALUES (1, 'Laptop', 899.99); -- alter Preis

-- MERGE: UPSERT ohne DELETE
MERGE INTO #TargetProducts AS T
USING #SourceProducts AS S
ON T.ProductID = S.ProductID
WHEN MATCHED THEN
  UPDATE SET T.ProductName = S.ProductName, T.Price = S.Price
WHEN NOT MATCHED THEN
  INSERT (ProductID, ProductName, Price)
  VALUES (S.ProductID, S.ProductName, S.Price);

-- Ergebnis: Produkt 1 wurde geupdatet, 2+3 wurden eingefügt.
SELECT * FROM #TargetProducts;

💡 Erklärung: Die MERGE-Anweisung vergleicht die Quell- und Zieltabelle über die ON-Bedingung. Bei Übereinstimmung UPDATE, sonst INSERT. Ideal für Datenübernahme aus Staging-Tabellen ohne Datenverlust.

⚡ 2. Vollständige Synchronisation – inklusive DELETE

📌 Ziel: Die Zieltabelle soll exakt dem Stand der Quelle entsprechen. Datensätze, die in der Quelle nicht mehr vorkommen, werden gelöscht.

TRUNCATE TABLE #TargetProducts;
INSERT INTO #TargetProducts VALUES (1, 'Laptop', 899.99), (99, 'Altes Produkt', 1.99);
TRUNCATE TABLE #SourceProducts;
INSERT INTO #SourceProducts VALUES (1, 'Laptop', 1099.99), (2, 'Maus', 24.99);

-- MERGE mit DELETE für Quelle-verwaiste Zeilen
MERGE INTO #TargetProducts AS T
USING #SourceProducts AS S
ON T.ProductID = S.ProductID
WHEN MATCHED THEN
  UPDATE SET T.ProductName = S.ProductName, T.Price = S.Price
WHEN NOT MATCHED BY TARGET THEN
  INSERT (ProductID, ProductName, Price) VALUES (S.ProductID, S.ProductName, S.Price)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

SELECT * FROM #TargetProducts;
-- Erwartetes Ergebnis: Nur ID 1 (upgedatet) und ID 2 (neu). ID 99 wurde gelöscht.

🧹 Erklärung: Mit NOT MATCHED BY SOURCE werden alle Zeilen in der Zieltabelle gelöscht, die in der Quelle nicht existieren. Dadurch entsteht eine 1:1-Übernahme – perfekt für Dimensions- oder Referenzdaten.

🧩 3. Komplexe Quelle – CTE oder Subquery als Datengrundlage

📌 Ziel: Die Quelle muss keine permanente Tabelle sein – sie kann eine CTE (Common Table Expression) oder eine Subquery sein. Das ermöglicht dynamische Aggregationen oder Joins direkt im Merge.

-- Beispiel: Umgesetzte Bestellungen aggregieren (Source = CTE)
CREATE TABLE #SalesTarget (ProductID INT, TotalSold INT);
INSERT INTO #SalesTarget VALUES (1, 10), (2, 5);

-- Temporäre Bestelldetails
CREATE TABLE #Orders (OrderID INT, ProductID INT, Quantity INT);
INSERT INTO #Orders VALUES (101,1,3),(102,1,2),(103,2,5),(104,3,7);

-- MERGE mit CTE als Quelle
WITH SourceAgg AS (
  SELECT ProductID, SUM(Quantity) AS TotalSold
  FROM #Orders
  GROUP BY ProductID
)
MERGE INTO #SalesTarget AS T
USING SourceAgg AS S
ON T.ProductID = S.ProductID
WHEN MATCHED THEN
  UPDATE SET T.TotalSold = S.TotalSold
WHEN NOT MATCHED BY TARGET THEN
  INSERT (ProductID, TotalSold) VALUES (S.ProductID, S.TotalSold)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

SELECT * FROM #SalesTarget ORDER BY ProductID;
-- Ergebnis: ID 1 -> 5, ID 2 -> 5, ID 3 -> 7 (neu), ID ? alte gelöscht

⚙️ Erklärung: Die CTE SourceAgg aggregiert die Bestellungen. Diese virtuelle Tabelle wird dann als Quelle für MERGE verwendet. So kannst du beliebig komplexe Logiken in die Quelle einfließen lassen – ohne permanente Zwischentabellen.

📊 4. Record Counting – Anzahl INSERT, UPDATE, DELETE ermitteln

📌 Ziel: Mit der OUTPUT-Klausel kann man nachvollziehen, welche Aktion pro Zeile ausgeführt wurde. Die Pseudospalte $action liefert 'INSERT', 'UPDATE' oder 'DELETE'. Zusammen mit einer temporären Tabelle oder einem Zähler erhält man granulare Statistiken.

-- Vorbereitung Testdaten
CREATE TABLE #Src (ID INT, Wert VARCHAR(20));
CREATE TABLE #Dst (ID INT PRIMARY KEY, Wert VARCHAR(20));
INSERT INTO #Src VALUES (1,'A'),(2,'B'),(3,'C');
INSERT INTO #Dst VALUES (1,'A_old'),(4,'X'),(5,'Y');

-- MERGE mit OUTPUT $action in eine Tischvariable (oder TempTable)
DECLARE @ChangeLog TABLE (ActionType VARCHAR(10), OldID INT, NewID INT);

MERGE INTO #Dst AS T
USING #Src AS S
ON T.ID = S.ID
WHEN MATCHED THEN
  UPDATE SET T.Wert = S.Wert
WHEN NOT MATCHED BY TARGET THEN
  INSERT (ID, Wert) VALUES (S.ID, S.Wert)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
OUTPUT $action AS AktionsTyp,
    CASE WHEN $action = 'UPDATE' THEN DELETED.ID END AS OldID,
    CASE WHEN $action IN ('INSERT','UPDATE') THEN INSERTED.ID END AS NewID
INTO @ChangeLog;

-- Auswerten der Zähler
SELECT 
  ActionType,
  COUNT(*) AS Anzahl
FROM @ChangeLog
GROUP BY ActionType;

-- Detail anzeigen (optional)
SELECT * FROM @ChangeLog;

-- Exakte Zählung über die Tischvariable
DECLARE @InsertCount INT, @UpdateCount INT, @DeleteCount INT;
SELECT 
  @InsertCount = COUNT(CASE WHEN ActionType = 'INSERT' THEN 1 END),
  @UpdateCount = COUNT(CASE WHEN ActionType = 'UPDATE' THEN 1 END),
  @DeleteCount = COUNT(CASE WHEN ActionType = 'DELETE' THEN 1 END)
FROM @ChangeLog;

PRINT 'INSERT: ' + CAST(@InsertCount AS VARCHAR) +
    ' | UPDATE: ' + CAST(@UpdateCount AS VARCHAR) +
    ' | DELETE: ' + CAST(@DeleteCount AS VARCHAR);
-- Ausgabe: INSERT: 1 | UPDATE: 1 | DELETE: 2

📈 Erklärung: Die OUTPUT $action Klausel fängt jede durchgeführte Aktion ab. Wir speichern sie in einer Tabellenvariable (@ChangeLog). Danach kann man aggregiert auswerten, wie viele INSERTs, UPDATEs und DELETEs tatsächlich stattgefunden haben – perfekt für Logging, Audit oder Meldungen in ETL-Prozessen.

💡 Tipp für den Alltag: Die MERGE Anweisung ist sehr mächtig, aber es gibt ein paar Fallstricke (z.B. tricky bei der Quellkardinalität). Nutzen Sie den OUTPUT Trick immer dann, wenn Sie eine Rückmeldung über die Anzahl der Änderungen benötigen – auch in gespeicherten Prozeduren bestens geeignet.

🎯 Bonus: Vollständiges Beispiel mit Fehlerbehandlung & COUNT als Resultset

📌 Ziel: Kompakte gespeicherte Prozedur, die eine MERGE Synchronisation durchführt und eine Ergebnismenge mit den Zählern zurückgibt.

CREATE OR ALTER PROCEDURE dbo.Sync_ProductCatalog
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Changes TABLE (Act VARCHAR(10));
  
  MERGE INTO dbo.Products AS T
  USING dbo.StagingProducts AS S
  ON T.ProductID = S.ProductID
  WHEN MATCHED AND (T.Name != S.Name OR T.Price != S.Price) THEN
    UPDATE SET T.Name = S.Name, T.Price = S.Price, T.ModifiedDate = GETDATE()
  WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Name, Price, CreatedDate)
    VALUES (S.ProductID, S.Name, S.Price, GETDATE())
  WHEN NOT MATCHED BY SOURCE AND T.IsActive = 1 THEN
    UPDATE SET T.IsActive = 0, T.DeletedDate = GETDATE()
  OUTPUT $action INTO @Changes;
  
  WITH Counts AS (
    SELECT Act, COUNT(*) AS Anzahl
    FROM @Changes
    GROUP BY Act
  )
  SELECT 
    ISNULL((SELECT Anzahl FROM Counts WHERE Act = 'INSERT'), 0) AS Inserted,
    ISNULL((SELECT Anzahl FROM Counts WHERE Act = 'UPDATE'), 0) AS Updated,
    ISNULL((SELECT Anzahl FROM Counts WHERE Act = 'DELETE'), 0) AS Deleted;
END;
GO

-- Aufruf liefert eine Zeile mit drei Spalten: Inserted, Updated, Deleted
EXEC dbo.Sync_ProductCatalog;

🚀 Erklärung: Dieses Prozedurbeispiel zeigt, wie man MERGE in der Praxis verwendet – mit zusätzlichen Bedingungen (AND in der MATCHED-Klausel, um unnötige Updates zu vermeiden) und weichem Löschen (Soft-Delete). Die OUTPUT‑Tabelle liefert die exakten Zähler, die am Ende als Resultset zurückgegeben werden.

⚡ Wichtige Hinweise zu MERGE: Achten Sie auf eindeutige Quellzuordnungen – wenn die USING-Quelle mehrere Zeilen mit dem gleichen Schlüssel liefert, kann MERGE mehrdeutig werden (Fehler!). Verwenden Sie nach Möglichkeit ROW_NUMBER() in der Quelle, um Duplikate zu beseitigen. Der OUTPUT $action Trick ist die beste Methode, um einsicht in die durchgeführten Operationen zu bekommen. Seit SQL Server 2016 ist MERGE auch bei System-Versioned Temporal Tables verfügbar.