-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
  • 10,349
  • 9
  • 44
  • 84
ZombieDivision
  • 183
  • 1
  • 11

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,870
  • 5
  • 26
  • 52
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;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • 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. – Thom A 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,547
  • 8
  • 20
  • 42
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
  • 565
  • 4
  • 15