48

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

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
pencilCake
  • 48,449
  • 79
  • 219
  • 356
  • http://stackoverflow.com/questions/6069024/syntax-of-for-loop-in-sql-server – Zam Feb 21 '14 at 08:21
  • 2
    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

5 Answers5

79

You may try it like this:

DECLARE @i int = 0
WHILE @i < 300 
BEGIN
    SET @i = @i + 1
    /* your code*/
END
Rahul Tripathi
  • 161,154
  • 30
  • 262
  • 319
27
DECLARE @first AS INT = 1
DECLARE @last AS INT = 300

WHILE(@first <= @last)
BEGIN
    INSERT INTO tblFoo VALUES(@first)
    SET @first += 1
END
124
  • 2,659
  • 24
  • 37
8

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

Ben
  • 50,172
  • 36
  • 122
  • 141
Tim Schmelter
  • 429,027
  • 67
  • 649
  • 891
  • 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
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

jean
  • 4,029
  • 4
  • 29
  • 50
Ramkumar Sambandam
  • 389
  • 1
  • 4
  • 11
-1

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
Freddie Wahlgren
  • 1