前言

UPSERT（Update + Insert）是資料同步、ETL、API 寫入的常見需求：主鍵存在則更新，不存在則插入，且需區分「資料完全相同無需動作」與「資料異動需更新」。本文提供一個生產就緒的 T-SQL 存儲過程範例，採用輸出參數回傳執行結果碼，清晰表達三種業務狀態。

一、業務邏輯定義

場景	判斷條件	動作	回傳碼
資料完全相同	主鍵存在且所有業務欄位值相等	無操作	0
主鍵存在但資料不同	主鍵存在且至少一業務欄位值不同	UPDATE	2
新資料	主鍵不存在	INSERT	1

二、存儲過程完整代碼

CREATE PROC [dbo].[Upsert_DemoData]
    @Id         VARCHAR(20),
    @Name       VARCHAR(20),
    @Telephone  VARCHAR(20),
    @Address    VARCHAR(20),
    @Job        VARCHAR(20),
    @ReturnCode INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CurName VARCHAR(20), @CurTel VARCHAR(20), @CurAddr VARCHAR(20), @CurJob VARCHAR(20);

    -- 1. 嘗試讀取現有資料
    SELECT @CurName = Name, @CurTel = Telephone, @CurAddr = Address, @CurJob = Job
    FROM dbo.DemoData WITH (UPDLOCK, HOLDLOCK)  -- 關鍵：避免並發競態
    WHERE Id = @Id;

    IF @@ROWCOUNT > 0
    BEGIN
        -- 2. 主鍵存在：比對業務欄位
        IF (@CurName = @Name AND @CurTel = @Telephone AND @CurAddr = @Address AND @CurJob = @Job)
        BEGIN
            SET @ReturnCode = 0;  -- 無變化
            RETURN;
        END

        -- 3. 有差異：執行更新
        UPDATE dbo.DemoData
        SET Name = @Name, Telephone = @Telephone, Address = @Address, Job = @Job
        WHERE Id = @Id;

        SET @ReturnCode = 2;  -- 更新成功
    END
    ELSE
    BEGIN
        -- 4. 主鍵不存在：插入新資料
        INSERT INTO dbo.DemoData (Id, Name, Telephone, Address, Job)
        VALUES (@Id, @Name, @Telephone, @Address, @Job);

        SET @ReturnCode = 1;  -- 插入成功
    END
END

三、呼叫範例

DECLARE @Ret INT;
EXEC dbo.Upsert_DemoData '15', 'hugh15', '3823345', '長安街', '副部長', @Ret OUTPUT;
SELECT @Ret AS ReturnCode;  -- 0=無變化 1=插入 2=更新

四、進階優化與注意事項

1. 並發安全：UPDLOCK, HOLDLOCK 提示

SELECT ... WITH (UPDLOCK, HOLDLOCK) 在讀取階段加更新鎖並持有至交易結束，防止「檢查不存在 → 並發插入 → 主鍵衝突」的競態條件。搭配顯式交易（或隱式自動提交）即可實現原子性 UPSERT。

2. MERGE 陳述式替代方案 (SQL Server 2008+)

單一陳述式完成同樣邏輯，原子性由引擎保證，程式碼更簡潔：

MERGE dbo.DemoData AS target
USING (VALUES (@Id, @Name, @Telephone, @Address, @Job)) AS src (Id, Name, Telephone, Address, Job)
ON target.Id = src.Id
WHEN MATCHED AND (target.Name <> src.Name OR target.Telephone <> src.Telephone OR target.Address <> src.Address OR target.Job <> src.Job)
    THEN UPDATE SET Name = src.Name, Telephone = src.Telephone, Address = src.Address, Job = src.Job
WHEN NOT MATCHED BY TARGET
    THEN INSERT (Id, Name, Telephone, Address, Job) VALUES (src.Id, src.Name, src.Telephone, src.Address, src.Job)
OUTPUT $action, inserted.Id;  -- 可解析回傳 INSERT/UPDATE/NO_ACTION

3. 擴充性考量

• 欄位較多時，可用 EXCEPT / INTERSECT 比對整行，或引入 HASHBYTES 比對檢查碼。
• 大量批次 UPSERT 建議使用 Table-Valued Parameter (TVP) + MERGE，減少網路往返。

結語

UPSERT 模式是資料整合的基石。採用「輸出參數回傳狀態碼」+「UPDLOCK, HOLDLOCK 防並發」的存儲過程寫法，相容性好、邏輯清晰、易於除錯；現代版本優先考慮 MERGE 簡化開發。根據專案 SQL Server 版本與團隊習慣選擇即可。