sql-server - while - sql loop through table and insert into another table




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

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


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

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


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




insert