插入大量数据之循环和游标

DROP TABLE #temp2

---随机取出有dealer employee的dealerid且状态均为active

DECLARE @t INT

DECLARE @d INT

SET @t=RAND()*100

SET @d=RAND()*1000

SELECT wp.WarrantyProductName,wp.WarrantyProductID,wc.WarrantyCodeID,wc.WarrantyCodeName,wl.WarrantyPlanID,wl.WarrantyPlanName,

d.DealerID,d.TradingName,de.DealerEmployeeID FROM dbo.WarrantyProducts wp ,dbo.WarrantyCode wc,dbo.WarrantyPlan wl,

dbo.Dealers d,dbo.DealerEmployees de

WHERE wp.WarrantyProductID=wc.WarrantyProductID

AND wp.WarrantyProductID=wl.WarrantyProductID AND wl.WarrantyPlanID=wc.WarrantyPlanID

AND d.DealerID=de.DealerID

AND wp.WarrantyProductID=@t

AND d.DealerID=@d

AND d.DealerStatusID=0

SELECT d.TradingName,d.DealerID,de.DealerEmployeeID  INTO #temp2 FROM dbo.Dealers d ,dbo.DealerEmployees de ,dbo.Users u

WHERE d.DealerID=de.DealerID

AND de.UserID=u.UserID

AND u.UserStatus=0

AND d.DealerStatusID=0

ALTER TABLE #temp2 ADD number INT IDENTITY(1,1)

ALTER TABLE #temp2 ADD salesrepid INT

---顺序取出sales repid进入#temp2，使得Salesrepid和Dealerid一一对应

DECLARE @salesrepid INT

DECLARE @aa INT

DECLARE @bb INT

SET @aa=1

SET @bb=1

WHILE @bb<52

BEGIN

DECLARE cursor1 CURSOR FOR

SELECT sp.SalesRepID FROM dbo.SalesReps sp JOIN dbo.Users u ON sp.UserID=u.UserID AND u.UserStatus=0

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @salesrepid

while @@fetch_status=0

AND @aa<64*@bb

BEGIN

UPDATE #temp2 SET #temp2.salesrepid=@salesrepid WHERE number=@aa

SET @aa=@aa+1

FETCH NEXT FROM cursor1 INTO @salesrepid

END

close cursor1

deallocate cursor1

SET @bb=@bb+1

END

---check #temp2数据

SELECT * FROM #temp2

----插入数据到Warranty表

DECLARE @tt INT

DECLARE @dd INT

DECLARE @test INT

SET @tt=RAND()*100

SET @dd=RAND()*1000

--PRINT @tt

SET @test=4000000

WHILE @test<4000010

BEGIN

INSERT INTO dbo.Warranty

( WarrantyProductID ,

WarrantyType ,

WarrantyProductType ,

WarrantyPlanID ,

WarrantyCodeID ,

WarrantyCodeName ,

DealerID ,

DealerEmployeeID ,

SaleRepID ,

WarrantyPrefix ,

WarrantyNumber ,

WarrantyFullNumber ,

InvoiceID ,

CommencementDate ,

ExpiryDate ,

CaravanMotorhomeCodeID ,

CaravanMotorhomeExpiryDate ,

TowVehicleWarrantyCodeID ,

TowVehicleWarrantyExpiryDate ,

ApplianceWarrantyCodeID ,

ApplianceWarrantyExpiryDate ,

WarrantyCaravanVehicleDetailID ,

WarrantyMotorhomeVehicleDetailID ,

WarrantyTowVehicleDetailID ,

WarrantyMonthlyFinanceDetailID ,

MonthlyFinancePaymentType ,

IsFinanced ,

FinancierID ,

CustomerCompany ,

CustomerSurName ,

CustomerGivenName ,

CustomerName ,

CustomerAddressID ,

ContactID ,

RegoNumber ,

VehicleManufacturerID ,

VehicleModelID ,

VehicleReleaseYear ,

VehicleDescriptionID ,

VehicleFuelTypeID ,

VehicleTransmissionTypeID ,

VehicleColourID ,

VehicleBodyTypeID ,

CylinderNumber ,

StockNo ,

MotorbikeFuelTypeID ,

IsAirCondition ,

IsPresitgeVehicle ,

PrestigeSurCharge ,

IsFourWheelExtra ,

FourWheelExtra ,

IsDiesel ,

DieselSurcharge ,

IsOtherSurcharges ,

OtherSurcharges ,

IsMarine ,

BoatEngineManufacturerID ,

BoatEngineModelID ,

BoatEngineType ,

MarineFuelTypeID ,

MarineRegoNumber ,

YearBuild ,

HoursePower ,

TypeOfUSe ,

BoatBrand ,

EngineSerialNumber ,

Drive ,

HourMeter ,

VINNumber ,

EngineNumber ,

StartOdometer ,

CurrentOdometer ,

ManufacturingDate ,

PurchaseDate ,

PurchasePrice ,

IsManufactureServcing ,

ServiceKilometer ,

ServiceMonths ,

RoadsideID ,

WarratyNetCost ,

GST ,

StampDuty ,

WarrantyCost ,

RoadsideCost ,

TotalWarrantyRoadsideCost ,

RetailPrice ,

UnReceiveAmount ,

DealerProductAdminFees ,

WarrantyWholesalePrice ,

ClaimAmountBalance ,

NumberOfClaimBalance ,

MaxAmountInSingleClaim ,

IsServiceBreach ,

OverServiceKm ,

OverServiceDay ,

IsAcceptServiceBreach ,

AcceptServiceBreachBy ,

IsVerified ,

PaymentStatus ,

IsExoTrabsfered ,

WarrantyStatus ,

LastUpdatedBy ,

IsExonetTransfered ,

IsLockServiceCriterion ,

IsLockTowVehicleServiceCriterion ,

ContractID_Old ,

IsExonetProcessed ,

OldDatabase ,

StatClaimAllowablePerWarrantyBalance ,

batch ,

Num ,

Pref ,

FullNo ,

IsLocked ,

LockedBy ,

LockedOn ,

MarineStroke ,

ServiceHours ,

CurrentHours ,

StartHours ,

OverServiceHours ,

EXONETTransferDate ,

CreateSourceType ,

DateEnter ,

InvoicedDealerID ,

RiskCarrierID ,

PrestigeFee ,

FourWheelFee ,

DieselFee ,

OtherFee ,

TotalOfSurchargeFee ,

GapCoverStartDate ,

GapCoverEndDate ,

CompanyID ,

OdometerExpiry ,

OdometerExpiryOverall ,

DeliveryDate ,

TotalRiskPool ,

UnderwrittenFee ,

DealerProductID ,

StartOdometer2 ,

ProductFeesID ,

DealerAward ,

EmployeeAward ,

IsManualServiceCheck ,

IsServicingAndTimeOfClaim ,

ModelSeries ,

HIN ,

IsVariableWholesalePrice ,

IsVariableAwardPoint ,

AttachingVehicleTypeID ,

WarrantyAttachingVehicleDetailID ,

AbnNumber ,

FSGVersion ,

DateFSGPrinted ,

DateFSGIssued ,

RepresentativeNumber ,

PendingDealerAwardPoint ,

PendingDealerEmployeeAwardPoint ,

PendingSaleRepCommission ,

IsNeedFSGDetail ,

WarrantyFullPaidDate ,

WarrantyFirstClaimDate ,

IsPendingCode ,

SubmitCode ,

MPLLU06 ,

MPLLU12 ,

MPLLU24 ,

MPLLU36 ,

MPLLU60 ,

MPLLPP12 ,

MPLLPP24 ,

MPLLPP36 ,

MPLLPP60 ,

MPLLPP120 ,

MPLLPR06 ,

MPLLPR12 ,

MPLLPR24 ,

MPLLPR36 ,

MPLLPR60 ,

MPLLP12 ,

MPLLP24 ,

MPLLP36 ,

MPLLP60 ,

TPP1A12 ,

TPP1A24 ,

TPP1A36 ,

TPP1D06 ,

TPP1D12 ,

TPP1D24 ,

TPP1D36 ,

TPP1D60 ,

TPP3D12 ,

TPP3D24 ,

TPP3D36 ,

TPP3D48 ,

TPP3D60 ,

TPP6D36 ,

TPP1L12 ,

TPP1L24 ,

TPP1L36 ,

TPP1L60 ,

TPP3L12 ,

TPP3L24 ,

TPP3L36 ,

TPP3L60 ,

EDDT06 ,

EDDT12 ,

EDDT24 ,

EDDT36 ,

EDDT60 ,

EDE06 ,

EDE12 ,

EDE24 ,

EDE36 ,

EDE60 ,

ELDT12 ,

ELDT24 ,

ELDT36 ,

ELDT60 ,

ELE12 ,

ELE24 ,

ELE36 ,

ELE60 ,

Z03 ,

ZPL03 ,

ZPL12 ,

ZPL24 ,

ZPL36 ,

ZPL48 ,

ZPL60 ,

ZDC36 ,

ZPPDC36 ,

ZPR72 ,

ZPRDC36 ,

ZPRDC60 ,

ZPDC36 ,

ZPRS12 ,

ZPRS36 ,

JACIA24 ,

MAP24 ,

EOMA12 ,

EOMA24 ,

EOMA36 ,

EOMA48 ,

EOMD06 ,

EOMD12 ,

EOMD15 ,

EOMD24 ,

EOMD36 ,

EOMD48 ,

EOMD60 ,

EOML12 ,

EOML24 ,

EOML36 ,

EOML48 ,

STATA ,

STATB ,

MSPRO24 ,

MSPLAT24 ,

AVWGD03 ,

AVWGS12 ,

AVWGU ,

AVWGDSTD12 ,

AVWGDSTD36

)

SELECT  wp.warrantyproductid , -- WarrantyProductID - int

wp.warrantytype , -- WarrantyType - int

wp.warrantyproducttype, -- WarrantyProductType - int

wl.warrantyplanid , -- WarrantyPlanID - int

wc.WarrantyCodeID , -- WarrantyCodeID - int

WarrantyCodeID , -- WarrantyCodeName - varchar(50)

#temp2.dealerid , -- DealerID - int

#temp2.dealeremployeeid , -- DealerEmployeeID - int

0 , -- SaleRepID - int

'nzz' , -- WarrantyPrefix - varchar(50)

@test , -- WarrantyNumber - varchar(50)

'nzz'+ CONVERT(VARCHAR (20),@test)  , -- WarrantyFullNumber - varchar(150)

NULL, -- InvoiceID - int

'2014-02-21 06:31:19' , -- CommencementDate - datetime

'2015-02-21 06:31:19' , -- ExpiryDate - datetime

NULL , -- CaravanMotorhomeCodeID - int

NULL  , -- CaravanMotorhomeExpiryDate - datetime

NULL , -- TowVehicleWarrantyCodeID - int

NULL , -- TowVehicleWarrantyExpiryDate - datetime

NULL  , -- ApplianceWarrantyCodeID - int

NULL , -- ApplianceWarrantyExpiryDate - datetime

NULL , -- WarrantyCaravanVehicleDetailID - int

NULL , -- WarrantyMotorhomeVehicleDetailID - int

NULL , -- WarrantyTowVehicleDetailID - int

NULL  , -- WarrantyMonthlyFinanceDetailID - int

NULL , -- MonthlyFinancePaymentType - int

0 , -- IsFinanced - bit

NULL , -- FinancierID - int

'nzz' , -- CustomerCompany - varchar(50)

'nzz surname' , -- CustomerSurName - varchar(50)

'nzzgivenname' , -- CustomerGivenName - varchar(50)

'nzzgivenname nzz surname' , -- CustomerName - varchar(150)

NULL  , -- CustomerAddressID - int

NULL  , -- ContactID - int

'nzz' , -- RegoNumber - varchar(50)

NULL , -- VehicleManufacturerID - int

NULL  , -- VehicleModelID - int

NULL  , -- VehicleReleaseYear - smallint

NULL  , -- VehicleDescriptionID - int

NULL  , -- VehicleFuelTypeID - int

NULL  , -- VehicleTransmissionTypeID - int

NULL  , -- VehicleColourID - int

NULL  , -- VehicleBodyTypeID - int

NULL  , -- CylinderNumber - int

'nzz stock no' , -- StockNo - varchar(10)

NULL  , -- MotorbikeFuelTypeID - int

0 , -- IsAirCondition - bit

0 , -- IsPresitgeVehicle - bit

NULL , -- PrestigeSurCharge - money

0, -- IsFourWheelExtra - bit

NULL , -- FourWheelExtra - money

0 , -- IsDiesel - bit

NULL , -- DieselSurcharge - money

0 , -- IsOtherSurcharges - bit

NULL , -- OtherSurcharges - money

0 , -- IsMarine - bit

NULL  , -- BoatEngineManufacturerID - int

NULL , -- BoatEngineModelID - int

NULL  , -- BoatEngineType - int

NULL  , -- MarineFuelTypeID - int

NULL  , -- MarineRegoNumber - varchar(50)

NULL , -- YearBuild - int

NULL  , -- HoursePower - varchar(50)

NULL  , -- TypeOfUSe - int

NULL  , -- BoatBrand - int

NULL  , -- EngineSerialNumber - varchar(50)

NULL , -- Drive - varchar(50)

NULL  , -- HourMeter - varchar(50)

'nzz vin' , -- VINNumber - varchar(50)

NULL , -- EngineNumber - varchar(50)

0 , -- StartOdometer - int

0 , -- CurrentOdometer - int

NULL  , -- ManufacturingDate - datetime

NULL, -- PurchaseDate - datetime

NULL , -- PurchasePrice - money

0 , -- IsManufactureServcing - bit

NULL, -- ServiceKilometer - int

NULL , -- ServiceMonths - int

NULL , -- RoadsideID - int

NULL , -- WarratyNetCost - money

NULL , -- GST - money

NULL , -- StampDuty - money

100 , -- WarrantyCost - money

NULL , -- RoadsideCost - money

100 , -- TotalWarrantyRoadsideCost - money

300 , -- RetailPrice - money

100 , -- UnReceiveAmount - money

NULL , -- DealerProductAdminFees - money

NULL , -- WarrantyWholesalePrice - money

NULL , -- ClaimAmountBalance - money

10, -- NumberOfClaimBalance - smallint

NULL , -- MaxAmountInSingleClaim - money

0 , -- IsServiceBreach - bit

0 , -- OverServiceKm - int

0 , -- OverServiceDay - int

0 , -- IsAcceptServiceBreach - bit

NULL , -- AcceptServiceBreachBy - int

0 , -- IsVerified - bit

0 , -- PaymentStatus - int

0, -- IsExoTrabsfered - bit

0 , -- WarrantyStatus - int

1, -- LastUpdatedBy - int

0 , -- IsExonetTransfered - bit

0, -- IsLockServiceCriterion - bit

0 , -- IsLockTowVehicleServiceCriterion - bit

NULL, -- ContractID_Old - int

NULL , -- IsExonetProcessed - bit

'' , -- OldDatabase - varchar(50)

NULL , -- StatClaimAllowablePerWarrantyBalance - money

0 , -- batch - int

'' , -- Num - varchar(30)

'' , -- Pref - varchar(100)

'' , -- FullNo - varchar(100)

0, -- IsLocked - bit

0 , -- LockedBy - int

NULL , -- LockedOn - datetime

NULL, -- MarineStroke - int

NULL , -- ServiceHours - int

NULL , -- CurrentHours - int

NULL , -- StartHours - int

NULL , -- OverServiceHours - int

NULL, -- EXONETTransferDate - datetime

0 , -- CreateSourceType - int

'2014-02-21 06:31:19' , -- DateEnter - datetime

0 , -- InvoicedDealerID - int

NULL, -- RiskCarrierID - int

NULL , -- PrestigeFee - money

NULL , -- FourWheelFee - money

NULL , -- DieselFee - money

NULL , -- OtherFee - money

NULL , -- TotalOfSurchargeFee - money

NULL , -- GapCoverStartDate - datetime

NULL , -- GapCoverEndDate - datetime

5 , -- CompanyID - int

NULL, -- OdometerExpiry - int

NULL , -- OdometerExpiryOverall - int

'2014-02-21 06:31:19' , -- DeliveryDate - datetime

NULL , -- TotalRiskPool - money

NULL , -- UnderwrittenFee - money

NULL , -- DealerProductID - int

0 , -- StartOdometer2 - int

NULL , -- ProductFeesID - int

NULL , -- DealerAward - decimal

NULL , -- EmployeeAward - decimal

0 , -- IsManualServiceCheck - bit

0 , -- IsServicingAndTimeOfClaim - bit

NULL , -- ModelSeries - varchar(100)

NULL , -- HIN - varchar(100)

0 , -- IsVariableWholesalePrice - bit

0 , -- IsVariableAwardPoint - bit

NULL , -- AttachingVehicleTypeID - int

NULL , -- WarrantyAttachingVehicleDetailID - int

NULL , -- AbnNumber - varchar(50)

NULL , -- FSGVersion - varchar(20)

NULL , -- DateFSGPrinted - datetime

NULL , -- DateFSGIssued - datetime

NULL, -- RepresentativeNumber - varchar(50)

NULL , -- PendingDealerAwardPoint - money

NULL , -- PendingDealerEmployeeAwardPoint - money

NULL , -- PendingSaleRepCommission - money

0 , -- IsNeedFSGDetail - bit

NULL , -- WarrantyFullPaidDate - datetime

NULL , -- WarrantyFirstClaimDate - datetime

NULL , -- IsPendingCode - bit

'' , -- SubmitCode - varchar(50)

'nzz test inset warranty' , -- MPLLU06 - varchar(500)

'nzz test inset warranty' , -- MPLLU12 - varchar(500)

'nzz test inset warranty' , -- MPLLU24 - varchar(500)

'nzz test inset warranty' , -- MPLLU36 - varchar(500)

'nzz test inset warranty' , -- MPLLU60 - varchar(500)

'nzz test inset warranty' , -- MPLLPP12 - varchar(500)

'nzz test inset warranty' , -- MPLLPP24 - varchar(500)

'nzz test inset warranty' , -- MPLLPP36 - varchar(500)

'nzz test inset warranty' , -- MPLLPP60 - varchar(500)

'nzz test inset warranty' , -- MPLLPP120 - varchar(500)

'nzz test inset warranty' , -- MPLLPR06 - varchar(500)

'nzz test inset warranty', -- MPLLPR12 - varchar(500)

'nzz test inset warranty' , -- MPLLPR24 - varchar(500)

'nzz test inset warranty' , -- MPLLPR36 - varchar(500)

'nzz test inset warranty' , -- MPLLPR60 - varchar(500)

'nzz test inset warranty', -- MPLLP12 - varchar(500)

'nzz test inset warranty' , -- MPLLP24 - varchar(500)

'nzz test inset warranty' , -- MPLLP36 - varchar(500)

'nzz test inset warranty' , -- MPLLP60 - varchar(500)

'nzz test inset warranty' , -- TPP1A12 - varchar(500)

'nzz test inset warranty' , -- TPP1A24 - varchar(500)

'nzz test inset warranty' , -- TPP1A36 - varchar(500)

'nzz test inset warranty' , -- TPP1D06 - varchar(500)

'nzz test inset warranty' , -- TPP1D12 - varchar(500)

'nzz test inset warranty' , -- TPP1D24 - varchar(500)

'nzz test inset warranty' , -- TPP1D36 - varchar(500)

'nzz test inset warranty' , -- TPP1D60 - varchar(500)

'nzz test inset warranty' , -- TPP3D12 - varchar(500)

'nzz test inset warranty' , -- TPP3D24 - varchar(500)

'nzz test inset warranty' , -- TPP3D36 - varchar(500)

'nzz test inset warranty' , -- TPP3D48 - varchar(500)

'nzz test inset warranty' , -- TPP3D60 - varchar(500)

'nzz test inset warranty' , -- TPP6D36 - varchar(500)

'nzz test inset warranty' , -- TPP1L12 - varchar(500)

'nzz test inset warranty' , -- TPP1L24 - varchar(500)

'nzz test inset warranty' , -- TPP1L36 - varchar(500)

'nzz test inset warranty' , -- TPP1L60 - varchar(500)

'nzz test inset warranty' , -- TPP3L12 - varchar(500)

'nzz test inset warranty' , -- TPP3L24 - varchar(500)

'nzz test inset warranty' , -- TPP3L36 - varchar(500)

'nzz test inset warranty' , -- TPP3L60 - varchar(500)

'nzz test inset warranty' , -- EDDT06 - varchar(500)

'nzz test inset warranty' , -- EDDT12 - varchar(500)

'nzz test inset warranty' , -- EDDT24 - varchar(500)

'nzz test inset warranty' , -- EDDT36 - varchar(500)

'nzz test inset warranty' , -- EDDT60 - varchar(500)

'nzz test inset warranty' , -- EDE06 - varchar(500)

'nzz test inset warranty' , -- EDE12 - varchar(500)

'nzz test inset warranty' , -- EDE24 - varchar(500)

'nzz test inset warranty' , -- EDE36 - varchar(500)

'nzz test inset warranty' , -- EDE60 - varchar(500)

'nzz test inset warranty' , -- ELDT12 - varchar(500)

'nzz test inset warranty' , -- ELDT24 - varchar(500)

'nzz test inset warranty' , -- ELDT36 - varchar(500)

'nzz test inset warranty' , -- ELDT60 - varchar(500)

'nzz test inset warranty' , -- ELE12 - varchar(500)

'nzz test inset warranty' , -- ELE24 - varchar(500)

'nzz test inset warranty' , -- ELE36 - varchar(500)

'nzz test inset warranty' , -- ELE60 - varchar(500)

'nzz test inset warranty' , -- Z03 - varchar(500)

'nzz test inset warranty' , -- ZPL03 - varchar(500)

'nzz test inset warranty' , -- ZPL12 - varchar(500)

'nzz test inset warranty' , -- ZPL24 - varchar(500)

'nzz test inset warranty' , -- ZPL36 - varchar(500)

'' , -- ZPL48 - varchar(500)

'' , -- ZPL60 - varchar(500)

'' , -- ZDC36 - varchar(500)

'' , -- ZPPDC36 - varchar(500)

'' , -- ZPR72 - varchar(500)

'' , -- ZPRDC36 - varchar(500)

'' , -- ZPRDC60 - varchar(500)

'' , -- ZPDC36 - varchar(500)

'' , -- ZPRS12 - varchar(500)

'' , -- ZPRS36 - varchar(500)

'' , -- JACIA24 - varchar(500)

'' , -- MAP24 - varchar(500)

'' , -- EOMA12 - varchar(500)

'' , -- EOMA24 - varchar(500)

'' , -- EOMA36 - varchar(500)

'' , -- EOMA48 - varchar(500)

'' , -- EOMD06 - varchar(500)

'' , -- EOMD12 - varchar(500)

'' , -- EOMD15 - varchar(500)

'' , -- EOMD24 - varchar(500)

'' , -- EOMD36 - varchar(500)

'' , -- EOMD48 - varchar(500)

'' , -- EOMD60 - varchar(500)

'' , -- EOML12 - varchar(500)

'' , -- EOML24 - varchar(500)

'' , -- EOML36 - varchar(500)

'' , -- EOML48 - varchar(500)

'' , -- STATA - varchar(500)

'' , -- STATB - varchar(500)

'' , -- MSPRO24 - varchar(500)

'' , -- MSPLAT24 - varchar(500)

'' , -- AVWGD03 - varchar(500)

'' , -- AVWGS12 - varchar(500)

'' , -- AVWGU - varchar(500)

'' , -- AVWGDSTD12 - varchar(500)

''  -- AVWGDSTD36 - varchar(500)

FROM dbo.WarrantyProducts wp ,dbo.WarrantyCode wc,dbo.WarrantyPlan wl,#temp2

WHERE wp.WarrantyProductID=wc.WarrantyProductID

AND wp.WarrantyProductID=wl.WarrantyProductID AND wl.WarrantyPlanID=wc.WarrantyPlanID

AND wp.WarrantyProductID=@tt

AND #temp2.number=@dd

SET @test=@test+1

END