合并数据

 数据库   ZeroIsStart   2024-10-20 09:43   20
  从入门到精通SQL Server

SQL Server 合并数据详解：使用 MERGE 语句合并数据

在 SQL Server 中，合并数据的需求非常常见，尤其是在数据同步、数据迁移以及处理重复记录时。为了简化这些场景下的操作，SQL Server 提供了 MERGE 语句。MERGE 语句允许将插入、更新和删除操作结合在一条语句中执行，从而减少了代码的复杂性，提升了执行效率。

本文将详细介绍如何使用 MERGE 语句合并数据，提供完整的示例，涵盖所有相关的语法、选项和使用场景，并结合事务及其他 SQL Server 组件进行深入解析。

1. MERGE 语句简介

MERGE 语句允许你在一条 SQL 语句中同时执行插入、更新或删除操作。它的工作原理是将一个源表与目标表进行匹配，依据匹配结果决定应该执行插入、更新还是删除操作。

基本语法：

MERGE INTO 目标表 AS target
USING 源表 AS source
ON 匹配条件
WHEN MATCHED THEN
    -- 匹配时执行的操作 (更新或删除)
WHEN NOT MATCHED BY TARGET THEN
    -- 当目标表中没有匹配时执行的操作 (插入)
WHEN NOT MATCHED BY SOURCE THEN
    -- 当源表中没有匹配时执行的操作 (删除)
  • 目标表：需要插入、更新或删除的表。
  • 源表：提供数据用于与目标表进行匹配的表。
  • 匹配条件：用于定义如何判断源表和目标表之间的匹配关系。
  • WHEN MATCHED：定义匹配时需要执行的操作（通常是更新或删除）。
  • WHEN NOT MATCHED BY TARGET：当目标表没有匹配的记录时，需要执行的操作（通常是插入）。
  • WHEN NOT MATCHED BY SOURCE：当源表没有匹配的记录时，需要执行的操作（通常是删除）。

2. MERGE 语句的基本使用

假设我们有两个表，Employees 表和 EmployeeUpdates 表。Employees 表存储现有的员工数据，而 EmployeeUpdates 表包含最新的员工数据。我们希望将 EmployeeUpdates 表中的数据合并到 Employees 表中，更新已有的员工数据，并插入新的员工记录。

表结构：

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(18, 2),
    HireDate DATETIME
);

CREATE TABLE EmployeeUpdates (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(18, 2),
    HireDate DATETIME
);

合并操作：

MERGE INTO Employees AS target
USING EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET
        target.FirstName = source.FirstName,
        target.LastName = source.LastName,
        target.Salary = source.Salary,
        target.HireDate = source.HireDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, FirstName, LastName, Salary, HireDate)
    VALUES (source.EmployeeID, source.FirstName, source.LastName, source.Salary, source.HireDate)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

解释：

  • WHEN MATCHED THEN UPDATE：更新 Employees 表中已存在的员工数据。
  • WHEN NOT MATCHED BY TARGET THEN INSERT：插入 EmployeeUpdates 表中不存在于 Employees 表的员工记录。
  • WHEN NOT MATCHED BY SOURCE THEN DELETE：删除 Employees 表中存在但不在 EmployeeUpdates 表中的员工记录。

3. MERGE 语句的详细解析

3.1 匹配条件 (ON)

MERGE 语句的核心是匹配条件。ON 子句用于指定如何将源表中的记录与目标表中的记录进行匹配。在上面的例子中，我们使用 EmployeeID 作为匹配条件：

ON target.EmployeeID = source.EmployeeID

这表示我们将根据 EmployeeID 匹配 Employees 表和 EmployeeUpdates 表中的记录。如果 EmployeeID 相同，则认为匹配成功。

3.2 匹配时的操作 (WHEN MATCHED THEN)

WHEN MATCHED 子句定义了当 MERGE 语句发现源表和目标表中有匹配记录时执行的操作。常见操作是 UPDATE 或 DELETE。

在上面的例子中，我们使用 UPDATE 来更新目标表中的记录：

WHEN MATCHED THEN
    UPDATE SET
        target.FirstName = source.FirstName,
        target.LastName = source.LastName,
        target.Salary = source.Salary,
        target.HireDate = source.HireDate;

如果你想在匹配时删除目标表中的记录，可以使用 DELETE 操作：

WHEN MATCHED THEN
    DELETE;

3.3 插入操作 (WHEN NOT MATCHED BY TARGET THEN)

WHEN NOT MATCHED BY TARGET 子句定义了当目标表中没有匹配记录时需要执行的操作，通常是 INSERT 操作。

WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, FirstName, LastName, Salary, HireDate)
    VALUES (source.EmployeeID, source.FirstName, source.LastName, source.Salary, source.HireDate);

此语句表示如果 Employees 表中不存在与 EmployeeUpdates 表中的 EmployeeID 相同的记录，则插入新的记录。

3.4 删除操作 (WHEN NOT MATCHED BY SOURCE THEN)

WHEN NOT MATCHED BY SOURCE 子句定义了当源表中没有匹配记录时需要执行的操作，通常是 DELETE 操作。这对于清理目标表中不再需要的记录非常有用。

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

4. MERGE 语句的进阶用法

4.1 使用 OUTPUT 子句返回合并结果

MERGE 语句支持使用 OUTPUT 子句返回操作结果，例如插入、更新或删除的记录。这对于调试或记录数据变更非常有用。

MERGE INTO Employees AS target
USING EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET
        target.FirstName = source.FirstName,
        target.LastName = source.LastName,
        target.Salary = source.Salary,
        target.HireDate = source.HireDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, FirstName, LastName, Salary, HireDate)
    VALUES (source.EmployeeID, source.FirstName, source.LastName, source.Salary, source.HireDate)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, inserted.*, deleted.*;

解释：

  • OUTPUT $action：返回合并操作的类型，如 INSERT、UPDATE 或 DELETE。
  • inserted.*：返回插入或更新后的新记录。
  • deleted.*：返回删除或更新前的旧记录。

4.2 使用 MERGE 实现审计日志

可以通过 MERGE 语句和 OUTPUT 子句结合，记录表数据的变更，以便实现审计日志功能。

MERGE INTO Employees AS target
USING EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET
        target.FirstName = source.FirstName,
        target.LastName = source.LastName,
        target.Salary = source.Salary,
        target.HireDate = source.HireDate
OUTPUT $action, inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.Salary, GETDATE() INTO AuditLog;

解释：

  • 此语句在合并数据的同时，将插入、更新或删除的操作记录到 AuditLog 表中，记录员工 ID、姓名、工资和操作日期。

5. 使用事务保证数据一致性

合并操作通常对数据有重要的影响，因此建议将 MERGE 语句放入事务中执行，以保证数据的一致性。在发生错误时，可以回滚事务，避免不完整的数据更新。

BEGIN TRANSACTION;

MERGE INTO Employees AS target
USING EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET
        target.FirstName = source.FirstName,
        target.LastName = source.LastName,
        target.Salary = source.Salary,
        target.HireDate = source.HireDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, FirstName, LastName, Salary, HireDate)
    VALUES (source.EmployeeID, source.FirstName, source.LastName

, source.Salary, source.HireDate)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

COMMIT TRANSACTION;

总结

MERGE 语句在 SQL Server 中为数据合并提供了强大的工具，可以在一条语句中高效地执行插入、更新和删除操作。通过使用 MERGE，我们可以简化数据同步逻辑，减少重复代码，同时提高数据处理的性能。在实际项目中，合理使用 MERGE 语句可以极大地提升数据库操作的效率和可维护性。

示例完整代码：

-- 示例：使用MERGE语句合并数据
MERGE INTO Employees AS target
USING EmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET
        target.FirstName = source.FirstName,
        target.LastName = source.LastName,
        target.Salary = source.Salary,
        target.HireDate = source.HireDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, FirstName, LastName, Salary, HireDate)
    VALUES (source.EmployeeID, source.FirstName, source.LastName, source.Salary, source.HireDate)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

通过掌握 MERGE 语句及其高级用法，开发者可以更加灵活地管理 SQL Server 中的数据合并操作。