Run insert statement x number of times

This is a hack and I wouldn't recommend using it in production or big volumes of data. However, in development quick-and-dirty scenarios I found it often useful:

Use GO \[count\] to execute a batch of commands a specified number of times.

Concretely, if you had a stored procedure called InsertAIntoB, you could run this in Management Studio:

exec InsertAIntoB
GO 10

(replace 10 with whatever NumInserts is)

create procedure insert_into_b
    @numInserts int
as
begin
    while @numInserts > 0
    begin
        insert into b (id)
        select id from a
        set @numInserts = @numInserts - 1
    end
end

exec insert_into_b 2

I prefer to avoid looping when I can, just so I don't have to maintain some easily breakable and somewhat ugly loop structure in my stored procedure.

You could easily do this with a Numbers table, the CROSS APPLY statement, and your existing INSERT statement.

Given that your numbers table would look like this:

Number
======
0
1
2
...

Your SQL statement simply becomes:

INSERT INTO B 
(
    [Col1]
    ,[Col2]
    ,[Col3]
    ,[Col4]
    ,[Col5]
)
SELECT 
    100 
    ,25
    ,'ABC'
    ,1
    ,a.ID
FROM 
    Auctions a
CROSS APPLY
    Numbers n
WHERE
    n.Number BETWEEN 1 AND @NumInserts

Numbers tables can be useful if use appropriately. If you're unfamiliar with them, here are a few resources and some pros/cons:

• http://dataeducation.com/you-require-a-numbers-table/ (the code to create a numbers table in this article is shown below)
• http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable
• https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

Maybe this solution is overkill if @NumInserts is always going to be a reasonably small number, but if you already have a Numbers table sitting around, you might as well take advantage of it!

UPDATE:

Here's a quick and dirty method to populate a numbers table from 0 to 65,535:

CREATE TABLE Numbers
(
    Number INT NOT NULL,
    CONSTRAINT PK_Numbers 
        PRIMARY KEY CLUSTERED (Number)
        WITH FILLFACTOR = 100
)
GO

INSERT INTO Numbers
SELECT
    (a.Number * 256) + b.Number AS Number
FROM 
(
    SELECT number
    FROM master..spt_values
    WHERE 
        type = 'P'
        AND number <= 255
) a (Number),
(
    SELECT number
    FROM master..spt_values
    WHERE 
        type = 'P'
        AND number <= 255
) b (Number)
GO

_{Credit: http://dataeducation.com/you-require-a-numbers-table/}