先看个例子:
declare @bid int = 20000030, @bidOld int = 18751388, @billid_temp int --创建游标aaa DECLARE aaa CURSOR FOR select [BillID] from bill where BusinessID = @bidOld --开打游标aaa Open aaa fetch next from aaa into @billid_temp --如果能从游标aaa取到值,那么@@FETCH_STATUS = 0 while @@FETCH_STATUS = 0 begin print @billid_temp INSERT [dbo].[billitem] ([BillID], [Subject], [DueDate], [Amount], [DueAmt], [ReceivedAmt], [CreateTime], [FullPaidTime], [Overdue], [SubjectType], [OperatorID], [IsCurrent], [IsShelve], [BusinessID], [PenaltyIntAmt], [LastCalcPenaltyDate], [SubjectKind], [FixedAmount], [LastCalcFloatAmtDate]) SELECT @billid_temp, [Subject], [DueDate], [Amount], [DueAmt], [ReceivedAmt], [CreateTime], [FullPaidTime], [Overdue], [SubjectType], [OperatorID], [IsCurrent], [IsShelve], @bid, [PenaltyIntAmt], [LastCalcPenaltyDate], [SubjectKind], [FixedAmount], [LastCalcFloatAmtDate] FROM PostLoan.dbo.billitem where BusinessID = @bidOld and billid = @billid_temp fetch next from aaa into @billid_temp --转到下一个游标,没有会死循环 end Close aaa Deallocate aaa
完整的插数脚本如下:
declare @bid int,@BillID int,@CustId int = 15834720, @bidOld int = 18751388,@billid_temp int,@billid_new int set @bid=20000030 while @bid<20000033 begin INSERT INTO dbo.business([BusinessID],[DSeqType],[PeriodType],[CustomerID],[SalesManID],[ProductType],[LoanCapital],[ResidualCapital],[LoanPeriod],[LoanTime],[InterestRate],[ServiceRate],[PenaltyRate],[AdvanceFee],[EarnestAmt],[Premium],[SecondSales],[BusinessStatus],[CLoanStatus],[LawsuitStatus],[ContractNo],[BankKey],[SavingCard],[LendingSideKey],[LendingSideID],[ServiceSideKey],[ServiceSideID],[GuaranteeSideKey],[GuaranteeSideID],[ConstructionBankNo],[ReceiptVersion],[OverMonth],[OverAmount],[CurrentOverAmount],[BranchKey] ,[LoanKind],[CreateTime],[SavingUser],[PaymentDate],[OtherAmount],[ToGuaranteeTime],[ToLitigationTime],[ClearLoanTime],[IsRepayment],[LawsuitCode],[ProceduresAmout],[IsFullInvoice],[GuaranteeNum],[FrozenNo],[ConstructSedNo],[SalesTeam],[DepositRate],[SignTime],[DucImgPath],[FromSource],[RelativeDate],[ProceduresRate],[SubBranch],[ProductKind],[PrincipalPunish],[ServicePunish],[SubmitTime],[NextBillDate],[SaleMode],[ToGuaranteeAmt],[Operable],[Region],[ClearLoanType],[ClearLoanRemark],[SpecailPolicy],[CapitalRate],[FundSource],[ManagementRate],[ChannelRate],[CollectionRate],[MeduimSource],[YearRate],[BreachingRate],[PlatformRate],[EvaluationCost],[DayRate],[BidBond],[LoanServiceAmout],[PeriodKind],[DayPeriod],[DayServiceRate],[BusinessSource],[GuaranteeRate],[DayPlatformRate],[DayRate2],[DayServiceRate2],[DayPlatformRate2],[CustomBillDay],[platformsidekey],[platformsideid],[platformsamount]) SELECT @bid,[DSeqType],[PeriodType],@CustId,[SalesManID],[ProductType],[LoanCapital],[ResidualCapital],[LoanPeriod],[LoanTime],[InterestRate],[ServiceRate],[PenaltyRate],[AdvanceFee],[EarnestAmt],[Premium],[SecondSales],[BusinessStatus],[CLoanStatus],[LawsuitStatus],[ContractNo],[BankKey],[SavingCard],[LendingSideKey],[LendingSideID],[ServiceSideKey],[ServiceSideID],[GuaranteeSideKey],[GuaranteeSideID],[ConstructionBankNo],[ReceiptVersion],[OverMonth],[OverAmount],[CurrentOverAmount],[BranchKey],[LoanKind],[CreateTime],[SavingUser],[PaymentDate],[OtherAmount],[ToGuaranteeTime],[ToLitigationTime],[ClearLoanTime],[IsRepayment],[LawsuitCode],[ProceduresAmout],[IsFullInvoice],[GuaranteeNum],[FrozenNo],[ConstructSedNo],[SalesTeam],[DepositRate],[SignTime],[DucImgPath],[FromSource],[RelativeDate],[ProceduresRate],[SubBranch],[ProductKind],[PrincipalPunish],[ServicePunish],[SubmitTime],[NextBillDate],[SaleMode],[ToGuaranteeAmt],[Operable],[Region],[ClearLoanType],[ClearLoanRemark],[SpecailPolicy],[CapitalRate],[FundSource],[ManagementRate],[ChannelRate],[CollectionRate],[MeduimSource],[YearRate],[BreachingRate],[PlatformRate],[EvaluationCost],[DayRate],[BidBond],[LoanServiceAmout],[PeriodKind],[DayPeriod],[DayServiceRate],[BusinessSource],[GuaranteeRate],[DayPlatformRate],[DayRate2],[DayServiceRate2],[DayPlatformRate2],[CustomBillDay],[platformsidekey],[platformsideid],[platformsamount] FROM PostLoan.dbo.business where BusinessID = @bidOld INSERT INTO dbo.businessextend([BusinessID],[LastReceiveCreatetime],[CreateTime],[IsByStages],[ProductAttribute],[CustomerCityId],[ExemptionDay],[StagesTime],[ExtendCount],[CouponAmt],[DownPaymentDate],[LoanAgreementNo],[AdvServiceRate],[AdvServiceFeeMin],[PTFormalitiesRate],[PTAdvServiceFeeMin],[PTAdvServiceRate],[SpendMoneyType],[channel],[GuarantPunish],[MultiCapitalWeight],[MultiCapitalFlag]) SELECT @bid,[LastReceiveCreatetime],[CreateTime],[IsByStages],[ProductAttribute],[CustomerCityId],[ExemptionDay],[StagesTime],[ExtendCount],[CouponAmt],[DownPaymentDate],[LoanAgreementNo],[AdvServiceRate],[AdvServiceFeeMin],[PTFormalitiesRate],[PTAdvServiceFeeMin],[PTAdvServiceRate],[SpendMoneyType],[channel],[GuarantPunish],[MultiCapitalWeight],[MultiCapitalFlag] FROM PostLoan.dbo.businessextend where BusinessID = @bidOld INSERT [dbo].[bill] ([BusinessID], [CustomerID], [BillType], [BillStatus], [BillMonth], [CompanyKey], [BeginTime], [EndTime], [LimitTime], [CreateTime], [OperatorID], [IsCurrent], [FullPaidTime], [IsShelve], [DeductionID], [IsFixed], [DueDate], [FullPaidDueDays], [GraceLimitTime], [IsAdvance], [IsRefund], [BillTypeSub], [CanceledTime], [CancelType], [CancelOperationId], [CancelDesc], [PaymentType], [BillIndex], [IsCanceled], [BillStatusA], [BillStatusB], [BillStatusSub], [ActiveTime]) SELECT @bid, @CustId, [BillType], [BillStatus], [BillMonth], [CompanyKey], [BeginTime], [EndTime], [LimitTime], [CreateTime], [OperatorID], [IsCurrent], [FullPaidTime], [IsShelve], [DeductionID], [IsFixed], [DueDate], [FullPaidDueDays], [GraceLimitTime], [IsAdvance], [IsRefund], [BillTypeSub], [CanceledTime], [CancelType], [CancelOperationId], [CancelDesc], [PaymentType], [BillIndex], [IsCanceled], [BillStatusA], [BillStatusB], [BillStatusSub], [ActiveTime] FROM PostLoan.dbo.bill where BusinessID = @bidOld --创建2个同步游标aaa和bbb分别取新旧bid的billid DECLARE aaa CURSOR FOR select [BillID] from bill where BusinessID = @bidOld DECLARE bbb CURSOR FOR select [BillID] from bill where BusinessID = @bid Open aaa Open bbb fetch next from aaa into @billid_temp fetch next from bbb into @billid_new while @@FETCH_STATUS = 0 begin print @billid_new INSERT [dbo].[billitem] ([BillID], [Subject], [DueDate], [Amount], [DueAmt], [ReceivedAmt], [CreateTime], [FullPaidTime], [Overdue], [SubjectType], [OperatorID], [IsCurrent], [IsShelve], [BusinessID], [PenaltyIntAmt], [LastCalcPenaltyDate], [SubjectKind], [FixedAmount], [LastCalcFloatAmtDate]) SELECT @billid_new, [Subject], [DueDate], [Amount], [DueAmt], [ReceivedAmt], [CreateTime], [FullPaidTime], [Overdue], [SubjectType], [OperatorID], [IsCurrent], [IsShelve], @bid, [PenaltyIntAmt], [LastCalcPenaltyDate], [SubjectKind], [FixedAmount], [LastCalcFloatAmtDate] FROM PostLoan.dbo.billitem where BusinessID = @bidOld and billid = @billid_temp fetch next from aaa into @billid_temp --转到下一个游标,没有会死循环 fetch next from bbb into @billid_new end Close aaa Deallocate aaa Close bbb Deallocate bbb print @bid set @bid=@bid+1 end
sql字符串,要向数据库中插入 字符串、日期,其两边是需要加上单引号的
参考:
https://www.cnblogs.com/xielong/p/5941595.html
https://cloud.tencent.com/developer/article/1455354