运行插入语句 x 次 [英] Run insert statement x number of times

问题描述

我有两张桌子.一张表A有n行数据，另一张表B为空.我想 insert n 行到表 B 中，表 A 中的每行 1 行.表 B 将包含来自表 A 的几个字段，包括来自表 A 的外键.

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.

最后，对于 A 中的每一行，我希望 B 中有一行.为此，我使用了:

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

现在，我把这段代码放在一个存储过程中，这个 SP 采用一个名为 NumInserts 的 int 参数.

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

我想插入 n * NumInserts 行.因此，如果 n 为 10 且 NumInserts 为 5，我想运行此代码 5 * 10 (50) 次.

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.

换句话说，对于table A 中的每一行，我想在table B 中insert 5 行.我该怎么做?

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

推荐答案

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

这篇关于运行插入语句 x 次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！