Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Run insert statement x number of times

Tags:

tsql

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?

like image 504
Sachin Kainth Avatar asked Nov 20 '12 15:11

Sachin Kainth


People also ask

How do I insert the same record multiple times in SQL?

INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.

Can you run multiple insert statements in SQL?

If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived table. Note that this INSERT multiple rows syntax is only supported in SQL Server 2008 or later. To insert multiple rows returned from a SELECT statement, you use the INSERT INTO SELECT statement.

How can I insert more than 1000 rows in SQL Server?

A table can store upto 1000 rows in one insert statement. If a user want to insert multiple rows at a time, the following syntax has to written. If a user wants to insert more than 1000 rows, multiple insert statements, bulk insert or derived table must be used.


1 Answers

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
like image 52
Clarice Bouwer Avatar answered Sep 28 '22 08:09

Clarice Bouwer