Run insert statement x times

Question

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

In the end, I want one row in B for each row in A For this, I used:

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

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

I want to insert n * NumInserts . 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 can I do it?

Answer 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 

Answer 2

I prefer to avoid the loop when I can, so I don’t need to maintain some easily destructible and somewhat ugly loop structure in my stored procedure.

You can easily do this with the Numbers table, the CROSS APPLY INSERT and the existing INSERT .

Given that the table of your numbers will look like this:

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

Your SQL statement just 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 

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

• http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx (the code for creating the table of numbers 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

Perhaps this solution is too large if @NumInserts will always be a small enough number, but if you already have a Numbers table, you can also use it!

UPDATE

Here is a quick and dirty method to populate a table of numbers from 0 to 65535:

 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://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx}

Answer 3

This is a hack, and I would not recommend using it in production or large amounts of data. However, in developing quick and dirty scripts, I found this often useful:

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

Specifically, if you had a stored procedure named InsertAIntoB , you can run it in Management Studio:

 exec InsertAIntoB GO 10 

(replace 10 with any NumInserts)

Answer 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 you are using SQL Server 2005 or earlier, replace Set @N -= 1' with Set @N = @ N-1`

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

  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 a cycle, like any solution to this problem.

Answer 5

A very late answer, but no need for a loop, and this is a bit 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 can use any table in a join if it has the number of rows you need. You can also change "1 [garbage]" to "ROW_NUMBER () OVER (ORDER BY object_id) [copyno]" if you want the copy number to be somewhere in the insert table.

Hope this saves someone a bit along the way ...