MERGE: eşitle, güncelle, ekle, sil

Tek ifadede üç işlem

MERGE, bir kaynak tablo ile hedef tabloyu bir anahtara göre karşılaştırır: eşleşenleri günceller, hedefte olmayanları ekler, istenirse kaynakta olmayanları siler. Veri ambarı yüklemelerinin temel aracıdır.

MERGE INTO hedef AS t
USING kaynak AS s
   ON t.anahtar = s.anahtar
WHEN MATCHED [AND ek_koşul] THEN UPDATE SET ...
WHEN NOT MATCHED [BY TARGET] THEN INSERT (...) VALUES (...)
WHEN NOT MATCHED BY SOURCE THEN DELETE;   -- MERGE ; ile bitmek zorundadır

En son fiyatları ürün tablosuna işlemek

WITH son AS (
    SELECT product_id, new_price,
           ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY updated_at DESC) AS rn
    FROM price_updates
)
MERGE INTO products AS t
USING (SELECT product_id, new_price FROM son WHERE rn = 1) AS s
   ON t.product_id = s.product_id
WHEN MATCHED AND t.price <> s.new_price THEN
    UPDATE SET price = s.new_price;

SELECT p.product_id, p.name, p.price
FROM products p
WHERE p.product_id IN (SELECT product_id FROM price_updates)
ORDER BY p.product_id;

Tam eşitleme: ekle, güncelle, sil

CREATE TABLE #hedef (dept_id INT PRIMARY KEY, name NVARCHAR(50), city NVARCHAR(50));
INSERT INTO #hedef SELECT dept_id, name, city FROM departments WHERE dept_id <= 4;
UPDATE #hedef SET city = N'Eski Şehir' WHERE dept_id = 2;

MERGE INTO #hedef AS t
USING (SELECT dept_id, name, city FROM departments WHERE dept_id >= 2) AS s
   ON t.dept_id = s.dept_id
WHEN MATCHED AND t.city <> s.city THEN UPDATE SET city = s.city
WHEN NOT MATCHED BY TARGET THEN INSERT (dept_id, name, city) VALUES (s.dept_id, s.name, s.city)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

SELECT * FROM #hedef ORDER BY dept_id;