-3

Desc I have table name: tableName and column name: columnName

Problem I need to create 450 lines and number them from 1 to 450.

I tried:

For(int i=1; i<451;i++)
{
  INSERT INTO tableName (columnName) VALUES i
}

for. exp.

IdP

1

2

3

...

Error: Could not find procedure

I don't know what procedure to use.

Prashant Pimpale
  • 9,685
  • 7
  • 35
  • 75
ZombieDivision
  • 183
  • 1
  • 9

4 Answers4

2

In SQL Server, you can use recursive cte instead :

with cte as (
     select 1 as start, 450 as loop_end
     union all
     select c.start + 1, loop_end
     from cte c
     where c.start < loop_end
)
INSERT INTO tableName (columnName)
   select c.start
   from cte c
   option (maxrecursion 0);
Yogesh Sharma
  • 49,081
  • 5
  • 23
  • 49
1

A tally is far faster at this than an rCTE.

If you wanted 450 rows, then this would work:

DECLARE @Rows int = 450;

WITH N AS(
    SELECT N 
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (@Rows) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3) --1000 rows, add more cross joins for more rows
SELECT I
FROM Tally;
Larnu
  • 76,706
  • 10
  • 34
  • 63
  • Is master..[spt_values] will be better here? I want your remarks. – Suraj Kumar Jan 30 '20 at 09:24
  • This won't result in a read from the disk, so *could* be faster, @SurajKumar . The easiest way to tell would be to test (with larger sets). Certainly if you are performing multiple cross joins to `spt_values` I would expect the above to be faster. – Larnu Jan 30 '20 at 09:33
  • OK that may be the point of testing, thanks. – Suraj Kumar Jan 30 '20 at 09:35
0

You can use the master..[spt_values] table. Here is the reference link. This will work if you have master database and access to that database.

SELECT DISTINCT n = number 
FROM master..[spt_values] 
WHERE number BETWEEN 1 AND 10

Live Demo

Suraj Kumar
  • 5,366
  • 8
  • 19
  • 39
0

Try using while loop:

declare @num int =1
while(@num <= 450)
begin
insert into table_name select @num
set @num = @num+1
end
Pankaj_Dwivedi
  • 555
  • 4
  • 15