Improving query to generate a million empty rows I have been asked to review a process that sadly is highly inefficient but we are held in the short term to the design. 

We currently generate based on a range a set of rows inside a table to hold future receipts. The ReceiptData table for every Receipt id has its rows generated first and as the receipts are use the next is populated.  Again this process for the short term is in stone. 

When a customer adds a receipt we then need to generate a million rows within the receiptData table that will then be used in the future. 

DECLARE @tempT TABLE 
( 
    number INT 
) 
DECLARE @recId AS INT -- represents an id a parent table id
DECLARE @receiptStart AS INT --represents where in the sequence to begin normmaly 1 but could be 1500 etc.
DECLARE @receiptEnd AS INT -- represents then end in relation to the start normally 999999 but could be 456789, defined by customer

SET @recId = 12
SET @receiptStart = 120
SET @receiptEnd = 999999

    DECLARE @iCounter AS INT = @receiptStart

WHILE ( @iCounter &lt;= @receiptEnd) 
  BEGIN 
      INSERT INTO @tempT 
      VALUES      (@iCounter) 
      SET @iCounter += 1 
  END 

INSERT receiptdata 
       (recid, 
        receiptnum, 
        negativereceipt) 
SELECT @recID, 
       Cast(number AS VARCHAR), 
       0 
FROM   @tempT 
WHERE  NOT EXISTS ( SELECT  1 
                    FROM    receiptdata 
                    WHERE   ffid = @recId
                            AND receiptnum = number
                  )


I ran this and it took over 13 minutes to process...there has to be a better way.  The initial population of @tempT takes over 30 seconds; the insert into ReceiptData takes the majority of the time. 

The annoying piece of this is the receiptnum within the receiptData table is a varchar.  while we plan out a future design we are stuck with all of this in the short term.  

Any suggestions? it it rather straight forward but insanely slow. 
we usually go with ranged from 1 to 999999 for receiptStart and receiptend.
To generate the 1 million rows for @tempT you could use a cartesian product of the master..spt_values table, like such:

declare @tempT table (number int)

declare @maxno int
select  @maxno = 1000000

insert into @tempT
select top(@maxno) row_number() over(order by v1.number)
from   master..spt_values v1,
       master..spt_values v2

select min(number),max(number),count(*) from @tempT

(No column name) (No column name) (No column name)
---------------- ---------------- ----------------
               1          1000000          1000000


Takes about 2 seconds to run this dbfiddle

NOTE: master..spt_values should have at least a few thousand records so a 2-way cartesian product should be sufficient to generate 1 million rows; if you need to go a lot higher you could provide an additional ,master..sp_values v3.

You could likely push this cartesian product into your insert receptdata / select query thus eliminating the requirement for the @tempT table variable, ymmv ...



The next (much bigger) performance issue is likely the 1 million not exists() operations.

Without knowing a bit more about the relationship between @recId and receiptdata.ffid it's kinda hard to address this query, eg, do you really need to test all 1 million @tempT rows against receiptdata?

Also, do you have an index on receiptdata the optimizer can use for the not exists() operations?