批量处理代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】批量处理

作者:大卫 / 发布于2016/12/20/ 895

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])

试试其它关键字

同语言下

可能有用的

大卫贡献的其它代码(18)