SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) 
 Mar  4 2020 00:59:26 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE dbo.RunningNumbers(Number INT NOT NULL
                               ,CalendarDate DATE NOT NULL
                               ,CalendarYear INT NOT NULL
                               ,CalendarMonth INT NOT NULL
                               ,CalendarDay INT NOT NULL
                               ,CalendarWeek INT NOT NULL
                               ,CalendarYearDay INT NOT NULL
                               ,CalendarWeekDay INT NOT NULL);

DECLARE @CountEntries INT = 100000;
DECLARE @StartNumber INT = 0;


WITH E1(N) AS(SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)), --10 ^ 1
    E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
    E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
    E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
    CteTally AS
    (
        SELECT TOP(ISNULL(@CountEntries,1000000)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1 + ISNULL(@StartNumber,0) As Nmbr
        FROM E8
    )
INSERT INTO dbo.RunningNumbers
SELECT CteTally.Nmbr,CalendarDate.d,CalendarExt.*
FROM CteTally
CROSS APPLY
(
    SELECT DATEADD(DAY,CteTally.Nmbr,{ts'1900-01-01 00:00:00'})
) AS CalendarDate(d)
CROSS APPLY
(
    SELECT YEAR(CalendarDate.d) AS CalendarYear
          ,MONTH(CalendarDate.d) AS CalendarMonth
          ,DAY(CalendarDate.d) AS CalendarDay
          ,DATEPART(WEEK,CalendarDate.d) AS CalendarWeek
          ,DATEPART(DAYOFYEAR,CalendarDate.d) AS CalendarYearDay
          ,DATEPART(WEEKDAY,CalendarDate.d) AS CalendarWeekDay
) AS CalendarExt;
100000 rows affected

create table ToBeGenerated(
year_id int
, week_number int
, good_id varchar(10)
, store_id int
, qty int
)

insert into ToBeGenerated values 
(2019, 42, '113466', 41, 7)
1 rows affected

Select 
      CalendarDate
      , year_id
      , week_number
      , good_id
      , store_id
      , 1 as qty
from dbo.RunningNumbers
inner join ToBeGenerated on CalendarYear = year_id and week_number = CalendarWeek


CalendarDate
year_id
week_number
good_id
store_id
qty


2019-10-13
2019
42
113466
41
1

2019-10-14
2019
42
113466
41
1

2019-10-15
2019
42
113466
41
1

2019-10-16
2019
42
113466
41
1

2019-10-17
2019
42
113466
41
1

2019-10-18
2019
42
113466
41
1

2019-10-19
2019
42
113466
41
1

CalendarDate	year_id	week_number	good_id	store_id	qty
2019-10-13	2019	42	113466	41	1
2019-10-14	2019	42	113466	41	1
2019-10-15	2019	42	113466	41	1
2019-10-16	2019	42	113466	41	1
2019-10-17	2019	42	113466	41	1
2019-10-18	2019	42	113466	41	1
2019-10-19	2019	42	113466	41	1