0°

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

- 声明变量
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
           ,'0'
           ,'0'
           ,'0'
           ,@ProductId
           ,@subTypeNo
           ,@PackageSpecID
           ,'0'
           ,''
           ,''
           ,''
           ,''
           ,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);
ORACLE
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论