批量处理的sql语句

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

转载于:https://www.cnblogs.com/wanyuan8/archive/2012/07/31/2616704.html