MERGE در SQL Server مدیریت و همگام سازی داده ها

بهینه‌سازی مدیریت داده‌ها: راهنمای کامل استفاده از دستور MERGE در SQL Server

دستور MERGE در SQL Server 2008 معرفی شد و بر اساس استاندارد ANSI SQL است. این دستور قدرتمند به شما امکان می‌دهد تا عملیات INSERT، UPDATE یا DELETE را روی یک جدول مقصد بر اساس نتایج یک JOIN با یک جدول منبع انجام دهید. این روش راهی کارآمد برای همگام‌سازی دو جدول است که اغلب برای مدیریت ابعاد متغیر آهسته (Slowly Changing Dimensions) یا عملیات upsert (به‌روزرسانی در صورت وجود، درج در صورت عدم وجود) استفاده می‌شود. استفاده از MERGE به یکپارچگی داده‌ها کمک کرده و پیچیدگی کدهای SQL را کاهش می‌دهد. در ادامه، ساختار، پارامترها و چندین مثال کاربردی از دستور MERGE را بررسی خواهیم کرد تا بتوانید به طور موثر از آن در پروژه‌های مدیریت داده خود بهره‌برداری کنید.

در اینجا ساختار کلی دستور MERGE آورده شده است. این ساختار نشان می‌دهد که چگونه می‌توان یک جدول مقصد را بر اساس داده‌های یک جدول منبع و با استفاده از شرایط مختلف (MATCHED, NOT MATCHED BY TARGET, NOT MATCHED BY SOURCE) تغییر داد:

MERGE target_table AS T
USING source_table AS S
ON (T.key_column = S.key_column)
WHEN MATCHED THEN
    UPDATE SET T.column1 = S.column1, T.column2 = S.column2, ...
WHEN NOT MATCHED BY TARGET THEN
    INSERT (column1, column2, ...) VALUES (S.column1, S.column2, ...)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, Inserted.*, Deleted.*;

برای درک بهتر، بیایید به توضیح پارامترهای کلیدی دستور MERGE بپردازیم:

`MERGE target_table AS T`

این بخش جدول مقصدی را که می‌خواهید عملیات INSERT، UPDATE یا DELETE را روی آن انجام دهید، مشخص می‌کند. این جدول نام مستعار (alias) `T` را به خود می‌گیرد که در ادامه دستور استفاده می‌شود.

`USING source_table AS S`

این بخش جدول منبع یا عبارت جدول را مشخص می‌کند که داده‌های آن برای مقایسه با جدول مقصد استفاده می‌شود. نام مستعار (alias) `S` به این جدول اختصاص داده می‌شود.

`ON ()`

شرط `ON` ستون‌های کلیدی (key columns) را مشخص می‌کند که برای مقایسه ردیف‌ها بین جدول مقصد و جدول منبع استفاده می‌شوند. این شرط تعیین می‌کند که کدام ردیف‌ها “تطابق یافته” (matched) یا “تطابق نیافته” (not matched) هستند.

`WHEN MATCHED THEN `

این بند برای زمانی است که یک ردیف در جدول منبع با یک ردیف در جدول مقصد بر اساس شرط `ON` تطابق دارد. در این حالت معمولاً یک عملیات `UPDATE` انجام می‌شود:

WHEN MATCHED THEN
    UPDATE SET T.column1 = S.column1, T.column2 = S.column2

همچنین می‌توانید برای حذف ردیف‌هایی که در هر دو جدول وجود دارند و مثلاً نیاز به به‌روزرسانی ندارند، از `DELETE` استفاده کنید:

WHEN MATCHED THEN
    DELETE

`WHEN NOT MATCHED BY TARGET THEN `

این بند زمانی اعمال می‌شود که یک ردیف در جدول منبع وجود دارد اما ردیف متناظری در جدول مقصد (بر اساس شرط `ON`) یافت نمی‌شود. در این حالت معمولاً یک عملیات `INSERT` انجام می‌شود:

WHEN NOT MATCHED BY TARGET THEN
    INSERT (column1, column2) VALUES (S.column1, S.column2)

`WHEN NOT MATCHED BY SOURCE THEN `

این بند زمانی استفاده می‌شود که یک ردیف در جدول مقصد وجود دارد اما ردیف متناظری در جدول منبع (بر اساس شرط `ON`) یافت نمی‌شود. این وضعیت معمولاً نشان‌دهنده ردیف‌هایی است که باید از جدول مقصد حذف شوند:

WHEN NOT MATCHED BY SOURCE THEN
    DELETE

`OUTPUT $action, Inserted.*, Deleted.*`

بند `OUTPUT` به شما امکان می‌دهد تا اطلاعاتی در مورد ردیف‌هایی که توسط دستور MERGE تحت تاثیر قرار گرفته‌اند، بازیابی کنید. می‌توانید از ستون‌های ویژه مانند `$action` (که نوع عملیات انجام شده را نشان می‌دهد: ‘INSERT’, ‘UPDATE’, ‘DELETE’) و همچنین `Inserted.*` (برای ردیف‌های جدید یا به‌روزرسانی شده) و `Deleted.*` (برای ردیف‌های حذف شده یا نسخه‌های قدیمی ردیف‌های به‌روزرسانی شده) استفاده کنید. این برای گزارش‌گیری یا ضبط تغییرات مفید است.

`OPTION ( [ ,…n ] )`

این بند اختیاری برای افزودن راهنمایی‌های کوئری (query hints) به دستور MERGE استفاده می‌شود تا بر نحوه اجرای آن توسط موتور پایگاه داده تأثیر بگذارد.

مثال ۱: درج، به‌روزرسانی و حذف با استفاده از دستور SQL MERGE

این مثال نحوه استفاده از دستور MERGE برای همگام‌سازی دو جدول را نشان می‌دهد که در آن ردیف‌ها درج، به‌روزرسانی و حذف می‌شوند. فرض کنید یک جدول `TargetProducts` و یک جدول `SourceProducts` داریم. می‌خواهیم `TargetProducts` را با `SourceProducts` همگام کنیم.

ابتدا جداول را ایجاد و داده‌های اولیه را وارد می‌کنیم:

CREATE TABLE TargetProducts (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

CREATE TABLE SourceProducts (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

-- درج داده‌های اولیه در TargetProducts
INSERT INTO TargetProducts (ProductID, ProductName, Price) VALUES
(1, 'لپ تاپ', 1200.00),
(2, 'موس', 25.00),
(3, 'کیبورد', 75.00);

-- درج داده‌های اولیه در SourceProducts
INSERT INTO SourceProducts (ProductID, ProductName, Price) VALUES
(1, 'لپ تاپ گیمینگ', 1500.00), -- به‌روزرسانی برای محصول ۱
(4, 'مانیتور', 300.00);      -- درج برای محصول ۴

اکنون، دستور MERGE را برای همگام‌سازی `TargetProducts` با `SourceProducts` اجرا می‌کنیم:

MERGE 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;

پس از اجرای MERGE، وضعیت `TargetProducts` را بررسی می‌کنیم:

SELECT * FROM TargetProducts;

خروجی نشان می‌دهد که محصول با ProductID 1 به‌روزرسانی شده، ProductID 4 درج شده و ProductID 2 و 3 (که در SourceProducts نبودند) حذف شده‌اند.

مثال ۲: استفاده از دستور SQL MERGE با بند OUTPUT

بند `OUTPUT` در دستور MERGE برای ضبط تغییراتی که توسط دستور MERجه انجام می‌شود، بسیار مفید است. این مثال نشان می‌دهد که چگونه می‌توان از آن برای ردیابی عملیات INSERT، UPDATE و DELETE استفاده کرد.

جدول‌های مشابه مثال ۱ را ایجاد می‌کنیم:

CREATE TABLE TargetProducts_Output (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

CREATE TABLE SourceProducts_Output (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

INSERT INTO TargetProducts_Output (ProductID, ProductName, Price) VALUES
(1, 'صندلی', 80.00),
(2, 'میز', 150.00);

INSERT INTO SourceProducts_Output (ProductID, ProductName, Price) VALUES
(1, 'صندلی ارگونومیک', 120.00), -- به‌روزرسانی
(3, 'کمد', 200.00);             -- درج

دستور MERGE را با بند `OUTPUT` اجرا می‌کنیم تا نوع عملیات و داده‌های تحت تاثیر را ببینیم:

MERGE TargetProducts_Output AS T
USING SourceProducts_Output 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
OUTPUT $action,
       ISNULL(Inserted.ProductID, Deleted.ProductID) AS ProductID_Affected,
       Inserted.ProductName AS NewProductName,
       Deleted.ProductName AS OldProductName,
       Inserted.Price AS NewPrice,
       Deleted.Price AS OldPrice;

خروجی MERGE نشان می‌دهد که چه عملیاتی (INSERT, UPDATE, DELETE) روی کدام ردیف‌ها انجام شده و چه مقادیری تغییر کرده‌اند.

مثال ۳: استفاده از MERGE فقط برای درج یا به‌روزرسانی

گاهی اوقات ممکن است بخواهید از MERGE فقط برای درج یا به‌روزرسانی ردیف‌ها استفاده کنید و عملیات حذف را نادیده بگیرید. این سناریو به عنوان عملیات “upsert” شناخته می‌شود.

جدول‌های آزمایشی را آماده می‌کنیم:

CREATE TABLE CustomerData (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    City VARCHAR(50)
);

CREATE TABLE NewCustomerUpdates (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    City VARCHAR(50)
);

INSERT INTO CustomerData (CustomerID, CustomerName, City) VALUES
(101, 'علی', 'تهران'),
(102, 'سارا', 'اصفهان');

INSERT INTO NewCustomerUpdates (CustomerID, CustomerName, City) VALUES
(101, 'علی رضایی', 'شیراز'), -- به‌روزرسانی
(103, 'مریم', 'مشهد');     -- درج

دستور MERGE را بدون بند `WHEN NOT MATCHED BY SOURCE THEN DELETE` اجرا می‌کنیم:

MERGE CustomerData AS T
USING NewCustomerUpdates AS S
ON T.CustomerID = S.CustomerID
WHEN MATCHED THEN
    UPDATE SET T.CustomerName = S.CustomerName, T.City = S.City
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, CustomerName, City) VALUES (S.CustomerID, S.CustomerName, S.City);

اکنون وضعیت `CustomerData` را مشاهده می‌کنیم:

SELECT * FROM CustomerData;

خواهید دید که `CustomerID` 101 به‌روزرسانی شده و 103 درج شده است، اما `CustomerID` 102 (که در `NewCustomerUpdates` نبود) حذف نشده و همچنان در `CustomerData` باقی مانده است.

مثال ۴: استفاده از MERGE با شرایط خاص

می‌توانید شرایط بیشتری را به بندهای `WHEN MATCHED` یا `WHEN NOT MATCHED` اضافه کنید تا کنترل دقیق‌تری بر عملیات داشته باشید. به عنوان مثال، می‌توانید فقط در صورتی به‌روزرسانی کنید که قیمت جدید متفاوت باشد یا فقط اگر یک محصول فعال باشد، آن را درج کنید.

فرض کنید می‌خواهیم فقط اگر قیمت محصول تغییر کرده باشد، آن را به‌روزرسانی کنیم:

CREATE TABLE Inventory (
    ItemID INT PRIMARY KEY,
    ItemName VARCHAR(100),
    Quantity INT,
    LastUpdated DATETIME
);

CREATE TABLE StagingInventory (
    ItemID INT PRIMARY KEY,
    ItemName VARCHAR(100),
    Quantity INT
);

INSERT INTO Inventory (ItemID, ItemName, Quantity, LastUpdated) VALUES
(1, 'قلم', 100, GETDATE()),
(2, 'دفترچه', 50, GETDATE());

INSERT INTO StagingInventory (ItemID, ItemName, Quantity) VALUES
(1, 'قلم آبی', 120),  -- به‌روزرسانی Quantity و ItemName
(3, 'پاک کن', 75);   -- درج

دستور MERGE با یک شرط اضافی در `WHEN MATCHED` برای بررسی تغییر در `Quantity`:

MERGE Inventory AS T
USING StagingInventory AS S
ON T.ItemID = S.ItemID
WHEN MATCHED AND (T.Quantity  S.Quantity OR T.ItemName  S.ItemName) THEN
    UPDATE SET T.Quantity = S.Quantity, T.ItemName = S.ItemName, T.LastUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ItemID, ItemName, Quantity, LastUpdated) VALUES (S.ItemID, S.ItemName, S.Quantity, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

وضعیت نهایی `Inventory` را بررسی می‌کنیم:

SELECT * FROM Inventory;

ملاحظات مهم در استفاده از دستور MERGE

هنگام استفاده از دستور MERGE، مهم است که برخی نکات را در نظر داشته باشید تا از عملکرد و یکپارچگی داده‌ها اطمینان حاصل کنید:

* **ACID Properties:** دستور MERGE یک عملیات اتمی (atomic operation) است. این به این معنی است که تمام عملیات INSERT، UPDATE و DELETE یا به طور کامل انجام می‌شوند یا هیچ کدام. این ویژگی اتمی بودن، یکپارچگی داده‌ها را تضمین می‌کند و از مشکلات ناشی از عملیات ناقص جلوگیری می‌کند.
* **Transaction Log:** مانند سایر دستورات تغییر داده، MERGE نیز عملیات خود را در لاگ تراکنش (transaction log) ثبت می‌کند. برای جداول بزرگ، این می‌تواند منجر به افزایش قابل توجه اندازه لاگ شود که باید مدیریت شود.
* **Locking:** MERGE می‌تواند قفل‌های (locks) پیچیده‌ای را روی جدول مقصد ایجاد کند، به خصوص اگر عملیات همزمان زیادی در حال اجرا باشد. این می‌تواند منجر به بن‌بست (deadlocks) یا مشکلات عملکردی شود. طراحی صحیح ایندکس‌ها و درک رفتار قفل‌گذاری برای بهینه‌سازی MERGE ضروری است.
* **Index Usage:** عملکرد دستور MERGE به شدت به کارایی ایندکس‌های موجود بر روی ستون‌های استفاده شده در شرط `ON` و `WHERE` وابسته است. اطمینان حاصل کنید که ایندکس‌های مناسبی بر روی جداول منبع و مقصد وجود دارد تا جستجوها و joinها به سرعت انجام شوند.
* **Order of Operations:** SQL Server ممکن است عملیات در دستور MERGE را به ترتیب خاصی پردازش کند. درک این ترتیب برای پیشگیری از رفتارهای غیرمنتظره و اطمینان از صحت منطق مهم است.
* **Error Handling:** حتماً مکانیزم‌های مناسب برای مدیریت خطاها در صورت بروز مشکل در اجرای MERGE را در نظر بگیرید، به خصوص در محیط‌های تولیدی.