SQL Server 表變數實戰：主從表同步更新的高效 UPSERT 模式

業務場景

訂單主表 MOrder 與明細表 MOrderDetail，使用者可新增/修改/刪除明細商品，需一次儲存同步兩表。

傳統作法缺點

-- 缺點：先刪後增會清除他人加的備註欄位
DELETE FROM MOrderDetail WHERE MOrderid = @id;
INSERT INTO MOrderDetail VALUES (@id, @pku1, @qty1, @remark1);
INSERT INTO MOrderDetail VALUES (@id, @pku2, @qty2, @remark2);
-- ... 大量重複 INSERT 陳述式

表變數優化方案：三步驟 MERGE 邏輯

-- 1. 宣告表變數暫存前端傳入的明細資料
DECLARE @tb TABLE (pku INT, qty INT, remark VARCHAR(200));

INSERT INTO @tb VALUES (@pku1, @qty1, @remark1);
INSERT INTO @tb VALUES (@pku2, @qty2, @remark2);
-- ... 批次塞入所有明細

-- 2. 更新既有明細 (JOIN 表變數)
UPDATE d SET 
    d.qty = t.qty, 
    d.remark = t.remark
FROM MOrderDetail d
INNER JOIN @tb t ON d.pku = t.pku
WHERE d.MOrderid = @id;

-- 3. 刪除前端未傳送的明細 (不在表變數中的商品)
DELETE FROM MOrderDetail
WHERE MOrderid = @id 
  AND pku NOT IN (SELECT pku FROM @tb);

-- 4. 新增全新明細 (表變數有但資料表無)
INSERT INTO MOrderDetail (MOrderid, pku, qty, remark)
SELECT @id, t.pku, t.qty, t.remark
FROM @tb t
WHERE NOT EXISTS (
    SELECT 1 FROM MOrderDetail d 
    WHERE d.MOrderid = @id AND d.pku = t.pku
);

表變數 vs 暫存表 比較

效能測試結論

經反覆測試，小量資料 (明細 < 500 筆) 表變數效能優於暫存表；大量資料或複雜查詢建議改用暫存表並建立索引。

適用場景總結

• 前端傳入完整明細清單需全量同步
• 明細筆數適中 (建議 < 1000)
• 需保留明細擴充欄位 (如備註、自訂屬性)
• 存儲過程內部處理，無跨存儲過程共享需求