SQL - INSERT SELECT two columns without duplicates-CodePudding

I am trying to do two subqueries to populate my table with two columns. wk_start and wk_end. Goal is to populate it with a specific range of date.

I've tried cross join but still having duplicates the same as this one

Here is my code

insert into bfs_dw.mpt_calendar (wk_start, wk_end)

select 

ter.wk_start,
ter1.wk_end

from

(select * from 
(select ADDDATE('2022-01-03', 7*(t4*10000   t3*1000   t2*100   t1*10   t0)) wk_start from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where wk_start < '2024-01-03'
order by wk_start ) ter

inner JOIN 

(select * from 
(select ADDDATE('2022-01-09', 7*(t4*10000   t3*1000   t2*100   t1*10   t0)) wk_end from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where wk_end < '2024-01-03'
order by wk_end ) ter1 ON 1 = 1

Output display below. which has a lot of duplicates after the insert.

What I am trying to do is this expected result, which has no duplicates once inserted:

Note: the two subqueries works well. Its output was the last image posted here.

CodePudding user response：

Use the first subquery to get wk_start, and add 6 days to get the wk_end

insert into bfs_dw.mpt_calendar (wk_start, wk_end)

select wk_start, wk_start   INTERVAL 6 DAY wk_end from 
(select ADDDATE('2022-01-03', 7*(t4*10000   t3*1000   t2*100   t1*10   t0)) wk_start from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where wk_start < '2024-01-03'