0

I want to insert multiple rows based on a limit value into a SQL Server table which has a column called Sequence.

The method which I am using is a WHILE loop, and my code is:

Declare @Limit bigint = 1, @Increment bigint = 0

While (@Increment <= (@Limit))
Begin
    Insert into MY_TABLE (Sequence) values (@Increment)

    Set @Increment = @Increment + 1
End

Please, is there any other way in SQL to achieve this row insert case without using loops?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sciguy13
  • 173
  • 1
  • 2
  • 8

1 Answers1

2

The following article should help: Generate a set or sequence without loops

In summary, the fastest way is to use a stacked CTE in conjunction with ROW_NUMBER():

DECLARE @Limit BIGINT = 10, 
        @Increment BIGINT  = 1;

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
N4 (N) AS (SELECT 1 FROM N3 AS N1 CROSS JOIN N3 AS N2),
N5 (N) AS (SELECT 1 FROM N4 AS N1 CROSS JOIN N4 AS N2)
INSERT INTO MY_TABLE (Sequence)
SELECT  TOP (@Limit / (@Increment + 1))
        ROW_NUMBER() OVER(ORDER BY N) * (@Increment + 1)
FROM    N5;
GarethD
  • 68,045
  • 10
  • 83
  • 123