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 =...
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;
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
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
