SQL Server同步SAP_Load到Premise表后更新源表标识报错求助

• 错误1：普通UPDATE语句误用MERGE专属的WHEN MATCHED语法，完全不符合SQL Server语法规范，且后续更新SAP_Load的语句中pt别名超出作用域无法识别
• 错误2：Premise表的索引错误创建到了SAP_Load表上，未按需求给目标表建立查询索引
• 错误3：插入Premise表时已经通过COALESCE(@oid, 0) + ROW_NUMBER()计算了正确的OBJECTID，后续多余执行UPDATE sde.Premise SET OBJECTID += @oid会导致所有OBJECTID重复累加，出现主键冲突
• 错误4：最后更新SAP_Load设置Transaction_Flag=2的语句中，未定义s别名，无法识别关联条件
• 错误5：向SAP_Load插入初始数据时，给整数类型的Transaction_Flag、Status传入空字符串，存在隐式转换风险

采用MERGE语句一次性处理匹配更新、不匹配插入的逻辑，同时通过OUTPUT子句捕获操作类型来更新源表，比分开执行效率更高，也能避免事务不一致问题：

-- 修正建表和初始化逻辑
CREATE TABLE sde.SAP_Load (
    Premise_ID nvarchar(50),
    Division nvarchar(15),
    InstallationType nvarchar(12),
    Transaction_Flag Integer,
    Status Integer
);

CREATE INDEX idx_SAP_Load_Source
ON sde.SAP_Load (Premise_ID, Division, InstallationType);

INSERT INTO sde.SAP_Load
VALUES 
('8000004592', 'WT', 'WATR', NULL, NULL),
('8000004592', 'WT', 'IRRG', NULL, NULL),
('8000004592', 'SW', 'WWTR', NULL, NULL),
('8000004593', 'WT', 'IRRG', NULL, NULL),
('8000004594', 'SW', 'WWTR', NULL, NULL);

CREATE TABLE sde.Premise (
    OBJECTID bigint NOT NULL PRIMARY KEY,
    Premise_ID nvarchar(50),
    Division nvarchar(15),
    InstallationType nvarchar(12),
);

-- 修正索引：给Premise表建联合索引
CREATE INDEX idx_Premise_Key
ON sde.Premise (Premise_ID, Division, InstallationType);

INSERT INTO sde.Premise
VALUES 
( 1, '8000004592', 'SW', 'WWTR'),
(2, '8000004592', 'WT', 'WATR');
GO

-- 定义临时表存储MERGE操作的输出结果
DECLARE @MergeOutput TABLE (
    ActionType nvarchar(10),
    Premise_ID nvarchar(50),
    Division nvarchar(15),
    InstallationType nvarchar(12)
);

-- 执行UPSERT操作
MERGE sde.Premise AS pt
USING sde.SAP_Load AS s
ON pt.Premise_ID = s.Premise_ID 
AND pt.Division = s.Division 
AND pt.InstallationType = s.InstallationType
WHEN MATCHED THEN
    UPDATE SET 
        -- 如有其他字段需要更新可在此处补充
        Premise_ID = s.Premise_ID
WHEN NOT MATCHED BY TARGET THEN
    INSERT (OBJECTID, Premise_ID, Division, InstallationType)
    VALUES (
        (SELECT ISNULL(MAX(OBJECTID),0) FROM sde.Premise) + ROW_NUMBER() OVER (ORDER BY s.Premise_ID, s.Division, s.InstallationType),
        s.Premise_ID, s.Division, s.InstallationType
    )
OUTPUT $action, inserted.Premise_ID, inserted.Division, inserted.InstallationType INTO @MergeOutput;

-- 给更新成功的源表记录打标记
UPDATE s
SET Transaction_Flag = 1, Status = 1
FROM sde.SAP_Load s
INNER JOIN @MergeOutput o 
ON s.Premise_ID = o.Premise_ID 
AND s.Division = o.Division 
AND s.InstallationType = o.InstallationType
WHERE o.ActionType = 'UPDATE';

-- 给插入成功的源表记录打标记
UPDATE s
SET Transaction_Flag = 2, Status = 1
FROM sde.SAP_Load s
INNER JOIN @MergeOutput o 
ON s.Premise_ID = o.Premise_ID 
AND s.Division = o.Division 
AND s.InstallationType = o.InstallationType
WHERE o.ActionType = 'INSERT';

执行后SAP_Load表2条匹配记录的Transaction_Flag为1，3条新增记录的Transaction_Flag为2，所有记录Status均为1，符合预期需求。

内容的提问来源于stack exchange，提问作者accedesoft