declare @EmpTemplateHistoryIDS nvarchar(max)='86e83923-47ac-4541-a124-c1136a0b5fa6';
declare @TablePayrollFixedItemHistory table
(
EmployeeID nvarchar(50),
EmptemplateHistoryID nvarchar(50),
ItemID nvarchar(50),
ItemName nvarchar(50),
OldItemValue nvarchar(50),
NewItemValue nvarchar(50),
CreateTime datetime,
LastUpdateTime datetime
)
---把要保存的固定项历史保存到临时表
insert into @TablePayrollFixedItemHistory
select b.EmployeeID,a.*
from
[PayrollFixedItemHistory] a,EmpPRTemplateHistory b
where a.EmpTemplateHistoryID=b.EmpTemplateHistoryID and a.EmpTemplateHistoryID
in (select value from UFun_SqlSplit(@EmpTemplateHistoryIDS,','))
select * from @TablePayrollFixedItemHistory
delete a from PayrollFixedItem a , @TablePayrollFixedItemHistory b
where a.EmployeeID=b.EmployeeID and a.ItemID not in(select ItemID from @TablePayrollFixedItemHistory where EmployeeID=a.EmployeeID)
UPDATE a SET
[ItemValue] = b.[NewItemValue],
[LastUpdateTime] = getdate()
FROM [PayrollFixedItem] a, @TablePayrollFixedItemHistory b
WHERE
a.[EmployeeID] = b.[EmployeeID] AND
a.[ItemID] = b.[ItemID]
INSERT INTO [PayrollFixedItem]
(
[EmployeeID],
[ItemID],
[ItemValue],
[CreateTime]
)
SELECT
[EmployeeID],
[ItemID],
[NewItemValue],
getdate()
FROM @TablePayrollFixedItemHistory a
WHERE NOT EXISTS
(SELECT * FROM [PayrollFixedItem] WHERE
[EmployeeID] = a.[EmployeeID] AND
[ItemID] = a.[ItemID])
