BEGIN TRAN declare @error int declare @CustomId varchar(100) declare @DefaultViewUrl varchar(100) declare @ChildCategory varchar(100) declare @Name varchar(100) declare @Description varchar(100) declare @InventoryLocation varchar(100) declare @City varchar(100) declare @Quantity int declare @ListPrice decimal(19, 5) declare @SalePrice decimal(19, 5) declare @Status int declare @Shape bigint declare @Diameter varchar(100) declare @Weight decimal(19, 5) declare @WeightUnit bigint declare @Color bigint declare @Clarity bigint declare @Cut bigint declare @Polishing bigint declare @Symmetry bigint declare @Fluorescence bigint declare @IsJIT bit declare @Depth varchar(100) declare @Girdle varchar(100) declare @Culet varchar(100) declare @PriceQualityIndex varchar(100) declare @InvestmentIndex varchar(100) declare @RecommendIndex varchar(100) declare @Category bigint declare @IsSingleton bit declare @MeasureMents varchar(100) declare @IsOrderedAndNoStore bit declare @Available bit declare @LowerHalf nvarchar(255) declare @Starlength nvarchar(255) declare @PavilionDepth nvarchar(255) declare @PavilionAngle nvarchar(255) declare @CrownHeight nvarchar(255) declare @CrownAngle nvarchar(255) declare @CertNo nvarchar(255) declare @CertType int declare @i int declare @number int declare @endNumber int declare @a nvarchar(255) declare @num nvarchar(255) declare @b nvarchar(255) set @i=1 set @endNumber=(select COUNT(*) from JITDiamondStagingTable) while @i<10--@endNumber begin dbcc checkident (inventorystone, reseed) --set @number=1053604 --set @CustomId = ('select @sqls top 1 CustomId from JITDiamondStagingTable where id in(select top '+ @i+ 'id from JITDiamondStagingTable order by ID DESC) order by ID DESC') set @a=('select top 1 @b=CustomId from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@CustomId output --set @Shape = (select max(Shape) from JITDiamondStagingTable) set @a=('select top 1 @b=Shape from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@Shape output --set @CertNo = (select max(CertNO) from JITDiamondStagingTable) set @a=('select top 1 @b=CertNo from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@CertNo output --set @Weight = (select CONVERT(decimal(19, 5), max([Weight])) from JITDiamondStagingTable) set @a=('select top 1 @b=Weight from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@Weight output --set @Color = (select CONVERT(int, max(Color)) from JITDiamondStagingTable) set @a=('select top 1 @b=Color from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@Color output --set @Clarity = (select CONVERT(int, max(Clarity)) from JITDiamondStagingTable) set @a=('select top 1 @b=Clarity from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@Clarity output --set @Cut = (select CONVERT(int, max(Cut)) from JITDiamondStagingTable) set @a=('select top 1 @b=Cut from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@Cut output --set @Symmetry = (select CONVERT(int, max(Symmetry)) from JITDiamondStagingTable) set @a=('select top 1 @b=Symmetry from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@Symmetry output --set @Polishing = (select CONVERT(int, max(Polishing)) from JITDiamondStagingTable) set @a=('select top 1 @b=Polishing from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@Polishing output --set @Fluorescence = (select CONVERT(int, max(Fluorescence)) from JITDiamondStagingTable) set @a=('select top 1 @b=Fluorescence from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@Fluorescence output --set @DEPTH = (select max(DEPTH) from JITDiamondStagingTable) set @a=('select top 1 @b=DEPTH from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@DEPTH output --set @SalePrice = (select max(SalePrice) from JITDiamondStagingTable) set @a=('select top 1 @b=SalePrice from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@SalePrice output --set @InventoryLocation = (select max(InventoryLocation) from JITDiamondStagingTable) set @a=('select top 1 @b=InventoryLocation from JITDiamondStagingTable where id in(select top '+CONVERT(nvarchar(255),@i)+' id from JITDiamondStagingTable order by ID DESC) order by ID DESC') exec sp_executesql @a,N'@b nvarchar(255) output',@InventoryLocation output insert into InventoryStone(CustomId,Shape,[Weight],Color,Clarity,Cut,Symmetry,Polishing,Fluorescence,Depth,SalePrice,Available) values(@CustomId,@Shape,@Weight,@Color,@Clarity,@Cut+@i,@Symmetry,@Polishing,@Fluorescence,@Depth,@SalePrice,'true') set @error = @@error if @error <> 0 begin print '插入失败' rollback tran end set @i=@i+1 end declare @inventoryStone_id int set @inventoryStone_id = (select top 1 Id from InventoryStone order by Id desc) --scope_identity() --@@a_id insert into DiamondCertification(DefaultStone,Number,Idx) values(@inventoryStone_id,@CertNo,'0') set @error = @@error if @error <> 0 begin print '插入失败 ' rollback tran end commit tran