SQL 批量更新插入,遍历

1）存储过程  传入参数 字符串,分割    获取插入临时表  临时表数据再插入表中，根据ID进行批量更新插入操作

ALTER PROCEDURE [dbo].[SP_PDA_UID_Collection]  
    (  
	  @partno NVARCHAR(max),
	  @carton Nvarchar(max),
	  @uid Nvarchar(max),
	  @operName Nvarchar(50)
    )  
AS   
    BEGIN  
   
        SET NOCOUNT ON ;  
        --创建临时表 
		CREATE TABLE #tmp_UID(
              PartNO  NVARCHAR(max),
              ContainerNO Nvarchar(max),
              [UID] Nvarchar(max),
              OperName Nvarchar(50) 
              )
        --存储扫描数据
	    INSERT INTO #tmp_UID  
       (        
              PartNO,
              ContainerNO,
              [UID],
              OperName
       )  
         SELECT                         
              t1.value,
              t2.value,
              t3.value,
              @operName 
              FROM dbo.Fn_Split(@partno,',') as t1 
			  inner join dbo.Fn_Split(@carton,',') as t2 on t1.id=t2.id
			  inner join dbo.Fn_Split(@uid,',') as t3 on t3.id=t2.id 
		--inner join dbo.Fn_Split(@operName,',') as t4 on t4.id=t3.id 
			  
	    --根据UID判断 进行更新插入操作
	    UPDATE A
             SET PartNO=B.PartNO,ContainerNO=B.ContainerNO,UpdateBy=B.OperName,UpdateDate=GETDATE() 
        FROM WMS_UID_Collection A
        INNER JOIN #tmp_UID B ON A.UID=B.UID
        
        INSERT INTO WMS_UID_Collection
             SELECT A.PartNO,A.ContainerNO,A.UID,0,A.OperName,GETDATE() as CreateDate,'' as UpdateBy,NULL as UpdateDate FROM #tmp_UID A
                    LEFT JOIN WMS_UID_Collection B ON A.UID=b.UID 
                    WHERE B.UID IS NULL
                    
        --移除临时表
		DROP TABLE 	#tmp_UID

    END                                    
             
  
  
  
  

2）创建历史表，插入数据，遍历临时表 获取行ID 进行更新操作

--更新备货详情表状态    
		CREATE TABLE #tmp_StockUpPart(
              OutboundOrderNO  NVARCHAR(100),
              PartNO NVARCHAR(100),
              InventoryID UNIQUEIDENTIFIER,
              Temp_PartID UNIQUEIDENTIFIER) 
              
		INSERT INTO #tmp_StockUpPart    --临时表存库存表关联的PartId                     
       (                     
        OutboundOrderNO,
        PartNO,
        InventoryID,    
        Temp_PartID
       )                                
        SELECT   
         p.[OutboundOrderNO],
         p.[PartNO],
         p.[InventoryID],
         p.[StockUpPartID]  FROM [P2WMS_WH71].[dbo].[WMS_Outbound_StockUpPick] as p 
         LEFT JOIN [P2WMS_WH71].[dbo].[WMS_Outbound_StockUp] as up  ON  up.StockUpOrderNo=p.OutboundOrderNo  
         WHERE  p.OutboundOrderNO=@OrderNo AND p.InventoryID=@InventoryID GROUP BY p.[OutboundOrderNO],p.[PartNO],p.[InventoryID],p.[StockUpPartID] ;
		
		DECLARE @Temp_PartID  NVARCHAR(100)
		DECLARE @temp_GIQty  int
		DECLARE @temp_PickEAQty  int
		
		WHILE EXISTS(SELECT Temp_PartID FROM #tmp_StockUpPart)  --遍历PartId
		
		   BEGIN 
                    SELECT @Temp_PartID= Temp_PartID FROM #tmp_StockUpPart;
                    
                    SELECT @temp_GIQty=SUM(GIQty) FROM [P2WMS_WH71].[dbo].[WMS_Outbound_StockUpPart] WHERE StockUpPartID=@Temp_PartID
                    SELECT @temp_PickEAQty=SUM(PickEAQty) FROM [P2WMS_WH71].[dbo].[WMS_Outbound_StockUpPick] WHERE StockUpPartID=@Temp_PartID and  MoveState =2 and OutboundOrderNO=@OrderNo
                    
                    IF(@temp_PickEAQty<@temp_GIQty)--部分移库
                    UPDATE [P2WMS_WH71].[dbo].[WMS_Outbound_StockUpPart] SET MoveState=1 WHERE StockUpPartID=@Temp_PartID 
                    ELSE                       --全部移库
                    UPDATE [P2WMS_WH71].[dbo].[WMS_Outbound_StockUpPart] SET MoveState=2 WHERE StockUpPartID=@Temp_PartID 
                    
                    
				 DELETE FROM #tmp_StockUpPart WHERE Temp_PartID=@Temp_PartID;
		   END
		DROP TABLE #tmp_StockUpPart