SQL Server 預存程序實現 UPSERT (插入或更新) 邏輯：三種回傳碼完整範例

UPSERT (UPDATE + INSERT) 是資料同步常見需求。本文提供標準預存程序實作，依主鍵判斷：資料完全相同 → 回傳 0；主鍵相同但資料不同 → 更新回傳 2；主鍵不存在 → 插入回傳 1。附完整建立與呼叫腳本。

業務邏輯

預存程序完整程式碼

CREATE PROCEDURE Insert_Update
    @Id VARCHAR(20),
    @Name VARCHAR(20),
    @Telephone VARCHAR(20),
    @Address VARCHAR(20),
    @Job VARCHAR(20),
    @returnValue INT OUTPUT
AS
BEGIN
    DECLARE @tmpName VARCHAR(20), @tmpTelephone VARCHAR(20),
            @tmpAddress VARCHAR(20), @tmpJob VARCHAR(20);

    IF EXISTS (SELECT * FROM dbo.DemoData WHERE id = @Id)
    BEGIN
        -- 讀取現有值
        SELECT @tmpName = Name, @tmpTelephone = Telephone,
               @tmpAddress = Address, @tmpJob = Job
        FROM dbo.DemoData WHERE id = @Id;

        -- 比較所有欄位
        IF (@tmpName = @Name AND @tmpTelephone = @Telephone
            AND @tmpAddress = @Address AND @tmpJob = @Job)
        BEGIN
            SET @returnValue = 0;  -- 完全相同，無操作
        END
        ELSE
        BEGIN
            UPDATE dbo.DemoData
            SET Name = @Name, Telephone = @Telephone,
                Address = @Address, Job = @Job
            WHERE id = @Id;
            SET @returnValue = 2;  -- 更新
        END
    END
    ELSE
    BEGIN
        INSERT INTO dbo.DemoData (Id, Name, Telephone, Address, Job)
        VALUES (@Id, @Name, @Telephone, @Address, @Job);
        SET @returnValue = 1;  -- 插入
    END
END
GO

呼叫方式

DECLARE @returnValue INT;
EXEC Insert_Update '15', 'hugh15', '3823345', '長安街', '副部長', @returnValue OUTPUT;
SELECT @returnValue AS ResultCode;
-- 0 = 已存在相同資料
-- 1 = 插入成功
-- 2 = 更新成功

進階優化方向

• MERGE 陳述式 (SQL 2008+)： 單一陳述式完成 UPSERT，原子性更強、避免競態條件。

  MERGE INTO dbo.DemoData AS target
  USING (VALUES (@Id, @Name, @Telephone, @Address, @Job))
         AS source (Id, Name, Telephone, Address, Job)
  ON target.Id = source.Id
  WHEN MATCHED AND (target.Name <> source.Name OR ...)
      THEN UPDATE SET ...
  WHEN NOT MATCHED
      THEN INSERT ...;

• 輸出子句： OUTPUT $action, inserted.*, deleted.* 記錄實際執行動作。
• 索引建議： Id 為主鍵/唯一索引，確保查找為 Seek。
• 並發控制： 高並發建議 UPDLOCK, HOLDLOCK 提示或可序列化隔離等級。

適用場景

• ETL 增量載入：來源資料與目標同步。
• 快取同步：應用層快取與資料庫雙寫。
• 多系統主鍵對齊：不同系統同步共用主鍵實體。

此 UPSERT 模式清晰、可擴展，配合 MERGE 進一步簡化邏輯並增強並發安全性，是資料同步作業的標準範本。