Insert into a table using CTE-CodePudding

I found a nice piece of code creating a table with 100.000 test records. I'm completely lost with the syntax. I know it's a table constructor (from values(0).....), I know it's a cross join (the part with: CreateTable as tt, CreateTable as st etc...), but how do I understand the formula creating 100.000 records?

truncate table t2;

with CreateTable as
(
    select d 
    from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as d(d)
)
insert into t2(col1)
    select 10000 * tt.d   1000 * st.d   100 * h.d   10 * t.d   s.d   1
    from CreateTable as tt, CreateTable as st, CreateTable as h, CreateTable as t, CreateTable as s;

CodePudding user response：

There is no formula to create 100 000 lines.

Just reading this I feel that: you begin with a table of 10 rows, then the code joins the table with itself with aliases (tt, st, h, t and s).

So there is 10 rows at the beginning and after 4 joins, this ends with 10^5 rows (100 000 rows).

As Stu said, this is a cartesian product.

CodePudding user response：

Assuming that you are using SQL Server:

Another possible solution without using cte, thought this might be helpful for others in future

create table dbo.test_table(id int)

declare @id int = 1

WHILE @id < 100001 
BEGIN  
        
        INSERT INTO test_table(id)
        SELECT  @id
        SET @id = @id 1
END