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

Question

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

http://stackoverflow.com/questions/6069024/syntax-of-for-loop-in-sql-server — Zam, Feb 21 '14 at 08:21
In SQL, you should generally try to write set-based code, not procedural, step by step code. So, the SQLish thing to ask for is "how do I insert 300 rows into my table, where each row gets a unique value from the range 1-300?" — Damien_The_Unbeliever, Feb 21 '14 at 08:25

score 93 · Accepted Answer · answered Feb 21 '14 at 08:25

93

You may try it like this:

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

answered Feb 21 '14 at 08:25

Rahul Tripathi

168,305
31
280
331

Isn't it possible to write the same loop by setting the variable using `SET` ? – aderchox Sep 13 '20 at 12:15
For many GUIDs use `insert into table_1 values(NEWID())`. – pbies Oct 13 '21 at 22:16

score 30 · Answer 2 · answered Feb 21 '14 at 08:37

30

DECLARE @first AS INT = 1
DECLARE @last AS INT = 300

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

answered Feb 21 '14 at 08:37

124

2,757
26
37

score 10 · Answer 3 · edited Feb 23 '14 at 02:06

10

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

edited Feb 23 '14 at 02:06

Ben

51,770
36
127
149

answered Feb 21 '14 at 08:26

Tim Schmelter

450,073
74
686
939

Thanks for the answer Tim. Question: So here do I assume there is 300 sys.all_objects for sure? What if I wanted to insert even 10 thousand? – pencilCake Feb 21 '14 at 08:29
@pencilCake: i recommend to read the article i've posted. It shows other techniques as well. Actually you'll get problems with above approach with more than two thousand. I'd use a numbers-table. – Tim Schmelter Feb 21 '14 at 08:31
1

On the other hand, if loops are not good idea in SQL, then I wonder why they introduced WHILE loops in T-SQL? – pencilCake Feb 21 '14 at 08:31
1

@pencilCake: there are things that aren't possible without loops or at least it isn't worth to implement a set based approach (which can take more time). So if you're doing this only once it might be sufficient to use a loop. But many people(programmers especially) are used with loops and keep using them in sql. – Tim Schmelter Feb 21 '14 at 08:32

score 6 · Answer 4 · edited Apr 20 '17 at 18:17

6

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

edited Apr 20 '17 at 18:17

jean

4,159
4
31
52

answered Apr 20 '17 at 17:54

Ramkumar Sambandam

487
1
6
11

score -2 · Answer 5 · edited May 04 '23 at 14:41

Found some different answers that I combined to solve similar 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 million rows with

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

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

5 Answers5

Linked