SQL

--插入新的发送礼包数据
INSERT INTO UserPrivilegeGiftPackageLog
  (
    RegNumber,
    PrivilegeId,
    GiftPackageId,
    [Status],
    Descn,
    CreateDate,
    EditDate,
    [ExpireDate]
  )
SELECT DISTINCT upgpl.RegNumber,
       upgpl.PrivilegeId,
       pgpm.GiftPackageId,
       0,
       upgpl.Descn,
       upgpl.CreateDate,
       upgpl.EditDate,
       upgpl.[ExpireDate]
FROM   Privilege_GiftPackage_Mapping AS pgpm
       INNER JOIN UserPrivilegeGiftPackageLog AS upgpl
            ON  pgpm.PrivilegeId = upgpl.PrivilegeId
WHERE  upgpl.[Status] = 0
       AND pgpm.IsDel = 0
       AND upgpl.PrivilegeId IN (167)
--把老的发送礼包数据置为失效       
UPDATE  upgpl
SET    [Status] = 2,
       EditDate = GETDATE()
       FROM UserPrivilegeGiftPackageLog as upgpl
WHERE  upgpl.PrivilegeId  IN (167)
       AND [Status] = 0
       AND GiftPackageId NOT IN (SELECT pgpm.GiftPackageId
                                 FROM   Privilege_GiftPackage_Mapping AS pgpm
                                 WHERE  pgpm.PrivilegeId =  upgpl.PrivilegeId 
                                        AND pgpm.IsDel = 0)