I need to generate 500 records using CTE, my output table should be something like below

I struggling with how to create it with cte as it has to be wrapped inside view

i created temp table its working fine but i wasnt able to wrap it inside view

i tried the following code

with cte1 as (
CREATE TABLE #code1(TEST,DESCRIPTION)
declare @TEST int
set @TEST = 1 
While @TEST <= 500
Begin 
    
    Insert Into #code1(TEST,DESCRIPTION) 
    values ('TEST'   CAST(@TEST as varchar(100)),'DESC'   CAST(@TEST as varchar(100)))
   Print @TEST
   Set @TEST = @TEST   1
End

)

I think I am doing it in the wrong way, can anyone give me any suggestions here.

CodePudding user response：

Your INSERT...SELECT syntax is all off. Start by creating the table normally, then INSERT the rows.

The best way to generate a lot of rows is to use a tally function.

...............