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

发布时间：2020-12-12 13:25:14 所属栏目：MsSql教程来源：网络整理

导读：-- 声明变量 DECLARE @SupCode as varchar(100),@ProdCode as varchar(50),@PackLayer as varchar(50),@CodeStatus as varchar(50),@ProductId as varchar(50),@subTypeNo as varchar(50),@PackageSpecID as varchar(50),@UpdateTime as varchar(50),@errorC

-- 声明变量 DECLARE @SupCode as varchar(100),@ProdCode as varchar(50),@PackLayer as varchar(50),@CodeStatus as varchar(50),@ProductId as varchar(50),@subTypeNo as varchar(50),@PackageSpecID as varchar(50),@UpdateTime as varchar(50),@errorCount as int=0; while exists(SELECT supcode FROM ##SupCode) ? ? begin -- 也可以使用top 1 SET ROWCOUNT 1 SELECT @SupCode=SupCode,@ProdCode=ProdCode,@PackLayer=PackLayer,@CodeStatus=CodeStatus,? ? ? @ProductId=ProductId,@subTypeNo=subTypeNo,@PackageSpecID=PackageSpecID,@UpdateTime=UpdateTime FROM ##SupCode; SET ROWCOUNT 0 begin transaction; --声明事物 INSERT INTO [Origin_System].[dbo].[Supcode] ? ? ? ? ? ?([SupCode] ? ? ? ? ? ?,[CheckPwd] ? ? ? ? ? ?,[ProdCode] ? ? ? ? ? ?,[PackLayer] ? ? ? ? ? ?,[CodeStatus] ? ? ? ? ? ?,[ParentCode] ? ? ? ? ? ?,[PrintBatchNo] ? ? ? ? ? ?,[TaskID] ? ? ? ? ? ?,[ProductId] ? ? ? ? ? ?,[subTypeNo] ? ? ? ? ? ?,[PackageSpecID] ? ? ? ? ? ?,[PackFlag] ? ? ? ? ? ?,[EquipmentID] ? ? ? ? ? ?,[ScanNo] ? ? ? ? ? ?,[BoxNo] ? ? ? ? ? ?,[UnitID] ? ? ? ? ? ?,[UpdateTime]) ? ? ?VALUES ? ? ? ? ? ?(@SupCode ? ? ? ? ? ?,'0' ? ? ? ? ? ?,@ProdCode ? ? ? ? ? ?,@PackLayer ? ? ? ? ? ?,@CodeStatus ? ? ? ? ? ?,@ProductId ? ? ? ? ? ?,@subTypeNo ? ? ? ? ? ?,@PackageSpecID ? ? ? ? ? ?,'' ? ? ? ? ? ?,GETDATE()); if(@@ERROR<>0) begin rollback transaction; set @errorCount=@errorCount+1; end else? begin commit transaction; end? DELETE FROM ##SupCode WHERE SupCode=@SupCode; ? ? end? print(@errorCount);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!