如何将跨库INSERT INTO语句改写为UPDATE实现数据同步？

原INSERT语句逻辑 #

原语句用于将源库中未删除的持卡人及对应卡号数据，解密姓名后插入到目标库的[ROCKFTINFO].[ShopDatabase].[dbo].[ref.Card]表中：

INSERT INTO
    [ROCKFTINFO].[ShopDatabase].[dbo].[ref.Card]
    (
        REF.[Card_ID]
        , REF.[FirstName]
        , REF.[LastName]
    )
SELECT
    CONVERT(nvarchar, DecryptByKey(CRH.FirstName)) AS [FirstName],
    CONVERT(nvarchar, DecryptByKey(CRH.LastName)) AS [LastName],
    LTRIM(RTRIM(CRD.CardNumber)) AS [Card_ID]
FROM 
    CardHolder AS CRH INNER JOIN
    Card AS CRD ON CRH.RecordID = CRD.CardHolderID
WHERE 
    (CRD.Deleted = 0) AND (CRH.Deleted = 0) AND (CRD.CardNumber > 0)

改写后的UPDATE语句 #

要实现源表数据变更时同步更新目标表，需使用SQL Server的UPDATE...FROM关联更新语法，通过Card_ID作为唯一匹配键关联目标表与源数据：

-- 注意：执行前需确保已打开解密密钥（原INSERT语句中也需要此操作）
-- OPEN SYMMETRIC Key Your_Key_Name DECRYPTION BY Password = 'Your_Password';

UPDATE REF
SET
    REF.[FirstName] = CONVERT(nvarchar, DecryptByKey(CRH.FirstName)),
    REF.[LastName] = CONVERT(nvarchar, DecryptByKey(CRH.LastName))
    -- 如果Card_ID可能从源端变更，可取消注释下面一行
    -- REF.[Card_ID] = LTRIM(RTRIM(CRD.CardNumber))
FROM
    [ROCKFTINFO].[ShopDatabase].[dbo].[ref.Card] AS REF
INNER JOIN
    Card AS CRD ON REF.[Card_ID] = LTRIM(RTRIM(CRD.CardNumber))
INNER JOIN
    CardHolder AS CRH ON CRH.RecordID = CRD.CardHolderID
WHERE
    (CRD.Deleted = 0) AND (CRH.Deleted = 0) AND (CRD.CardNumber > 0)
    -- 可选：仅更新确实有数据变化的行，减少不必要的更新操作
    -- AND (REF.[FirstName] != CONVERT(nvarchar, DecryptByKey(CRH.FirstName)) 
    --      OR REF.[LastName] != CONVERT(nvarchar, DecryptByKey(CRH.LastName)))

关键说明 #

• 关联逻辑：通过目标表的Card_ID与源表处理后的CardNumber匹配，确保更新正确的行。
• 解密密钥：由于使用了DecryptByKey，执行前必须先打开对应的对称密钥，否则解密会失败。
• 可选优化：添加字段比对的WHERE条件，只更新有实际变化的行，提升性能并减少日志量。
• 特殊场景：如果源端的CardNumber（即目标表的Card_ID）可能发生变更，可取消Card_ID更新行的注释，但需确保Card_ID不是目标表的自增主键或有约束限制修改。

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