大数据量插入

DECLARE @ww INT ,@ii INT,@inv INT
SET @ww=4398769
 --(SELECT TOP 1 warrantyid FROM warranty WHERE WarrantyFullNumber LIKE 'nzz%')
 SET @inv=465117
  --SELECT TOP 1 InvoiceID FROM dbo.Invoice ORDER BY InvoiceID DESC
SET @ii=1018199
WHILE @ww<=4780571
--(SELECT TOP 1 warrantyid FROM warranty WHERE WarrantyFullNumber LIKE 'nzz%' ORDER BY WarrantyID DESC)
BEGIN
 
 INSERT INTO dbo.Invoice
         ( InvoiceNumber ,
           InvoiceType ,
           InvoicedDate ,
           DelaerID ,
           SalesrepID ,
           TotalWarranties ,
           TotalPackagedRA ,
           TotalStandaloeRA ,
           Totalwarrantycost ,
           TotalRSACost ,
           TotalUnReceiveAmount ,
           TotalAmount ,
           GST ,
           GrandTotal ,
           IsAccountDealer ,
           AccountDealerMonthlyStatementID ,
           InvoiceStatus ,
           CreateBy ,
           IsExonetTransfered ,
           EXONETTransactionID ,
           InvoicePaymentAllocateMain ,
           AttachManualScannedInvoicePath ,
           IsExonetProcessed ,
           olddatabase ,
           batch ,
           IsException ,
           RealDealerID ,
           EXONETTransferDate ,
           LastUpdateOn ,
           LastUpdateBy ,
           CompanyID ,
           InvoiceContractType
         )
 VALUES  ( 'itest'+CONVERT(VARCHAR(40),@ii) , -- InvoiceNumber - varchar(50)
           0 , -- InvoiceType - int
           '2013-05-10 07:13:56' , -- InvoicedDate - datetime
           10525 , -- DelaerID - int
           9 , -- SalesrepID - int
           2 , -- TotalWarranties - int
           0 , -- TotalPackagedRA - int
           0 , -- TotalStandaloeRA - int
           600, -- Totalwarrantycost - money
           0 , -- TotalRSACost - money
           600, -- TotalUnReceiveAmount - money
          600 , -- TotalAmount - money
           100 , -- GST - money
          20 , -- GrandTotal - money
           0 , -- IsAccountDealer - bit
           NULL , -- AccountDealerMonthlyStatementID - int
           0 , -- InvoiceStatus - int
           1 , -- CreateBy - int
           0 , -- IsExonetTransfered - bit
           NULL, -- EXONETTransactionID - int
           NULL , -- InvoicePaymentAllocateMain - varchar(50)
           NULL , -- AttachManualScannedInvoicePath - varchar(500)
           NULL , -- IsExonetProcessed - bit
           NULL , -- olddatabase - varchar(50)
           NULL , -- batch - nvarchar(50)
           NULL , -- IsException - bit
           NULL , -- RealDealerID - int
           NULL , -- EXONETTransferDate - datetime
           NULL, -- LastUpdateOn - datetime
           NULL , -- LastUpdateBy - int
           1, -- CompanyID - int
           0  -- InvoiceContractType - int
         )
         UPDATE dbo.Warranty SETInvoiceID=@inv
         WHEREwarranty.WarrantyID=@ww
          
         SET @ww=@ww+1
                  
         SET @ii=@ii+1
         SET @inv=@inv+1
END

 

 

 

在update warranty 时候，尽量用变量值，不要直接用select语句，实际试验表明：select invoiceid from invoice where invoicenumber='nzz10000'， 执行15分钟，只生成了2万多条数据；而如果用常量或者变量代替，4分钟运行产生了40万条数据