sqlserver造订单类关联数据

首先生成一个1~100万的单列表，这个脚本转自 http://blog.itpub.net/37724/viewspace-997822/

set nocount on
use PostLoan
go

if object_id('dbo.nums') is not null
drop table dbo.nums
go

create table dbo.nums(n int not null primary key)
declare @max as int, @rc as int;
set @max=1000000
set @rc=1

insert into nums values(1)
while @rc*2<=@max
begin
insert into dbo.nums select n+@rc from dbo.nums
set @rc=@rc*2
end

insert into dbo.nums
select n+@rc from dbo.nums where n+@rc<=@max

select n from dbo.nums

[@more@]

 

然后用这个列和业务数据连接，可以造出100万的业务数据，业务数据可通过 n +-*/ 来得到

from nums，业务表 (不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积，请参考 https://www.cnblogs.com/jepson6669/p/9425491.html)

下面是一个实例

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 n+18751388,[DSeqType],[PeriodType],15834720,[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 dbo.nums,PostLoan.dbo.business
where BusinessID = 18751388


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 n+18751388,[LastReceiveCreatetime],[CreateTime],[IsByStages],[ProductAttribute],[CustomerCityId],[ExemptionDay],[StagesTime],[ExtendCount],[CouponAmt],[DownPaymentDate],[LoanAgreementNo],[AdvServiceRate],[AdvServiceFeeMin],[PTFormalitiesRate],[PTAdvServiceFeeMin],[PTAdvServiceRate],[SpendMoneyType],[channel],[GuarantPunish],[MultiCapitalWeight],[MultiCapitalFlag] 
FROM dbo.nums,PostLoan.dbo.businessextend 
where BusinessID = 18751388


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 n+18751388, 15834720, [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 dbo.nums,PostLoan.dbo.bill
where BusinessID = 18751388


DECLARE @j INT
set @j=0
while @j<4
begin
    INSERT [dbo].[billitem] ([BillID], [Subject], [DueDate], [Amount], [DueAmt], [ReceivedAmt], [CreateTime], [FullPaidTime], [Overdue], [SubjectType], [OperatorID], [IsCurrent], [IsShelve], [BusinessID], [PenaltyIntAmt], [LastCalcPenaltyDate], [SubjectKind], [FixedAmount], [LastCalcFloatAmtDate])
    SELECT 83636374+n+@j*1000000, [Subject], [DueDate], [Amount], [DueAmt], [ReceivedAmt], [CreateTime], [FullPaidTime], [Overdue], [SubjectType], [OperatorID], [IsCurrent], [IsShelve],  n+18751388, [PenaltyIntAmt], [LastCalcPenaltyDate], [SubjectKind], [FixedAmount], [LastCalcFloatAmtDate]
    FROM nums,PostLoan.dbo.billitem 
    where billid = 83636374+@j
print @j
set @j=@j+1
END

测试几分钟就可以插入100万条数据，非常方便