Run insert statement x number of times

问题:

I have two tables. One table A has n rows of data and the other table B is empty. I want to insert n rows into table B, 1 row for each row in table A. Table B will have a couple of fields from table A in it, including a foreign key from table A.

In the end I want one row in B for each row in A. To do this I used:

INSERT INTO B(Col1
             ,Col2
             ,Col3
             ,Col4
             ,Col5
             );
SELECT 100
      ,25 
      ,'ABC'
      ,1
      ,A.ID
FROM Auctions A

Now, I've put this code in a stored procedure and this SP takes an int param called NumInserts.

I want to insert n * NumInserts rows. So, if n is 10 and NumInserts is 5 I want to run this code 5 * 10 (50) times.

In other words for each row in table A I want to insert 5 rows in table B. How would I do that?

回答1:

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

回答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/}

回答3:

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)

回答4:

 Create procedure DoitNTimes 
 @N integer = 1
 As
 Set NoCount On

    While @N > 0 Begin
       Insert B (Col1, Col2, Col3, Col4, Col5)
       Select 100, 25, 'ABC', 1, A.ID
       From Auctions A
       -- -----------------------------------
       Set @N -= 1
    End

If using SQL Server 2005 or earlier replace the Set @N -= 1' withSet @N = @N-1`

and if you really want to avoid loop using T-SQL variables, then use a CTE, not a disk-based table:

 Create procedure DoitNTimes 
 @N integer = 1
 As
 Set NoCount On

     With nums(num) As
       (Select @N Union All
        Select num - 1
        From nums
        Where num > 1)
     Insert B (Col1, Col2, Col3, Col4, Col5)
     Select 100, 25, 'ABC', 1, A.ID
     From Auctions A Full Join nums
     Option(MaxRecursion 10000) 

but of course, this is also still looping, just like any solution to this issue.

回答5:

Very late answer but there is no need to loop and it's a little simpler than Corey's good answer;

DECLARE @n int = 10;
INSERT INTO B(Col1,Col2,Col3,Col4,Col5);
SELECT 100,25,'ABC',1,A.ID
FROM Auctions A
JOIN (SELECT TOP(@n) 1 [junk] FROM sys.all_objects) as copies ON 1 = 1

You could use any table in the join as long as it has the number of rows you'll need. You could also change "1 [junk]" to "ROW_NUMBER() OVER(ORDER BY object_id) [copyno]" if you wanted a copy number somewhere in the insert table.

Hopefully this will save someone a little work down the road...