How can INSERT INTO a table 300 times within a loop in SQL?

I would like to insert a value retrieved from a counter in SQL and repeat it 300 times.

Something like:

DECLARE @Counter = 0; -- BEGIN Loop SET @Counter = @Counter + 1 INSERT INTO tblFoo VALUES(@Counter) -- REPEAT 300 times 

How can I achieve this? Thanks

5 Answers

You may try it like this:

DECLARE @i int = 0 WHILE @i < 300 BEGIN SET @i = @i + 1 /* your code*/ END 

DECLARE @first AS INT = 1 DECLARE @last AS INT = 300 WHILE(@first <= @last) BEGIN INSERT INTO tblFoo VALUES(@first) SET @first += 1 END 

I would prevent loops in general if i can, set approaches are much more efficient:

INSERT INTO tblFoo SELECT TOP (300) n = ROW_NUMBER()OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n; 

Demo

Generate a set or sequence without loops

In ssms we can use GO to execute same statement

Edit This mean if you put

 some query GO n 

Some query will be executed n times

Found some different answers that I combined to solve simulair problem:

CREATE TABLE nummer (ID INTEGER PRIMARY KEY, num, text, text2); WITH RECURSIVE for(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM for WHERE i < 1000000) INSERT INTO nummer SELECT i, i+1, "text" || i, "otherText" || i FROM for; 

Adds 1 miljon rows with

• id increased by one every itteration
• num one greater then id
• text concatenated with id-number like: text1, text2 ... text1000000
• text2 concatenated with id-number like: otherText1, otherText2 ... otherText1000000