sqlserver 循环临时表修改插入数据到另一张表

爱被打了一巴掌 2022-11-12 08:27 385阅读 0赞

## 使用存储过程利用临时表循环实现多表修改update ##

CREATE PROCEDURE [dbo].[UP_Pack_Detail_Update]
    (
    	@Types varchar(100) = NULL
    )
    AS
    SET XACT_ABORT ON## 标题
    	BEGIN TRAN
    
    --构建临时表
    SELECT ROW_NUMBER() OVER ( ORDER BY id) i,* INTO #A FROM 
    [pack_detail] where pack_no=@Types --in('N-C161--210318-010-001','N-C161--210318-010-002','N-C161--210318-010-003','N-C161--210318-010-004','N-C161--210318-010-005')
    				DECLARE @count INT;
    				SELECT @count=COUNT(1) FROM #A --获取临时表#A 的总数量，用来循环； 
    				DECLARE @i INT=1;
    				WHILE(@i<=@count)                                  --循坏
    						BEGIN
    							DECLARE @thickness_grade2 NVARCHAR(200) = NULL;
    							DECLARE @thickness_grade NVARCHAR(200); 
    							DECLARE @thickness_avg  float;
    							DECLARE @thickness_avg2  float;
    							DECLARE @out_code NVARCHAR(200); 
    							DECLARE @pack_no NVARCHAR(200); 
    							
    							SELECT @out_code=m_no,@pack_no=pack_no FROM #A where i=@i--获得临时表相应数据
    	select @thickness_grade2=thickness_grade_2,@thickness_grade=thickness_grade_1,@thickness_avg=thickness_avg_1,@thickness_avg2=thickness_avg_2 
    	from split_detail where out_code=@out_code
    							print @out_code
    							print @pack_no
    							print @thickness_avg
    
    							update [pack_detail] set thickness_grade=@thickness_grade,thickness_grade2=@thickness_grade2,thickness_avg=@thickness_avg,thickness_avg2=@thickness_avg2 
    							where  pack_no=@pack_no and m_no=@out_code
    							--避免死循环 @i +1；
    							SET @i=@i+1;         
    							
    							SELECT * FROM #A 
    						END
    
    						--select * from [pack_detail] where pack_no in('N-415-495-210204-001-003') 
    				--千万要记得删除临时表 #A，否则下次执行会报错：“数据库中已存在名为'#A' 的对象”。
    				DROP TABLE #A 
    
    	COMMIT TRAN
    GO