SQL Server 2016

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

select @@version;


(No column name)


Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) 
	Feb 15 2020 01:47:30 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE booking
(
  bk_no     INTEGER NOT NULL PRIMARY KEY,
  bk_nights INTEGER NOT NULL CHECK (bk_nights > 0 AND bk_nights < 100), -- 100 arbitrary
  bk_sdt    DATE NOT NULL
);
INSERT INTO booking
VALUES
(1000, 5, '2021-05-06'),
(1001, 3, '2021-05-06');
2 rows affected

CREATE TABLE booking_nights
(
  nt_no    INTEGER NOT NULL,
  bk_no    INTEGER NOT NULL,
  bk_night DATE NOT NULL,
  
  PRIMARY KEY (bk_no, bk_night)
);
WITH cte (n, bkno, bknt, bkdt) AS
(
  SELECT
    1,
    bk_no, 
    bk_nights,
    bk_sdt
  FROM booking
  UNION ALL
  SELECT
    n + 1,
    bkno,
    bknt,
    DATEADD(DAY, 1, bkdt)
  FROM cte
  WHERE n < bknt
        
)
INSERT INTO booking_nights
SELECT c.n, c.bkno, c.bkdt 
FROM cte c
ORDER BY c.bkno, c.bkdt;
8 rows affected

SELECT * FROM booking_nights
ORDER BY bk_no, bk_night;


nt_no
bk_no
bk_night


1
1000
2021-05-06

2
1000
2021-05-07

3
1000
2021-05-08

4
1000
2021-05-09

5
1000
2021-05-10

1
1001
2021-05-06

2
1001
2021-05-07

3
1001
2021-05-08

nt_no	bk_no	bk_night
1	1000	2021-05-06
2	1000	2021-05-07
3	1000	2021-05-08
4	1000	2021-05-09
5	1000	2021-05-10
1	1001	2021-05-06
2	1001	2021-05-07
3	1001	2021-05-08