What is the T-SQL syntax to fill the columns of a table in my database with the same set of data with continuous dates?

Question

I am using SQL Server 2016 and I need to create a table (RoomInventory) in my database that will contain the following set of data which will be repeated for the period 01 January 2016 to 31 December 2016.

Here is how I want the final table to appear:

Date         RoomType    Property    Inventory 
2016-01-01   SUP            JS          20
2016-01-01   DLX            JS          15
2016-01-01   FAS            FB           6

2016-01-02   SUP            JS          20
2016-01-02   DLX            JS          15
2016-01-02   FAS            FB           6
-------------------------------------------
-------------------------------------------
2016-12-31   SUP            JS           20
2016-12-31   DLX            JS           15
2016-12-31   FAS            FB            6

I know how to create the Table and how to insert data using the INSERT INTO syntax but I have no clue as to how to write the proper syntax that will fill the DATE column with the dates required.

There is no command for filling ranges. Simplest way usually is to manually create a calendar table that has one row per date first and use that. — James Z, Jun 17 '16 at 17:01
@JamesZ I do have a calendar table in my database. How do I link my set of data with the Date Column of my Calendar Table to get the RoomInventory Table? — user3115933, Jun 17 '16 at 17:04

score 4 · Accepted Answer · answered Jun 17 '16 at 17:06

4

Use a recursive CTE:

;WITH
    cte AS
    (
        SELECT      CAST('2016-01-01' AS date)      AS [Date]
        UNION ALL
        SELECT      DATEADD(DAY, 1, [Date])
        FROM        cte
        WHERE       [Date] < '2016-12-31'
    )

SELECT      *
FROM        cte
CROSS JOIN  RoomInventory
OPTION      (MAXRECURSION 0)

answered Jun 17 '16 at 17:06

Code Different

90,614
16
144
163

This is actually somewhat eye-opening for me, as I've never seen a recursive CTE, this works very well and I've already made myself a snippet that uses the pattern to recreate c#'s `Enumerable.Range()` in SQL. Many thanks. – Max Sorin Jun 17 '16 at 17:59

score 3 · Answer 2 · answered Jun 17 '16 at 17:09

I like the CTE option more for the Calendar... here's my go at it.

DECLARE @Start DATE, @End DATE

SET @Start = '2016-01-01'
SET @End   = '2016-12-31'

IF OBJECT_ID('tempdb..#date') IS NOT NULL
    DROP TABLE #date
CREATE TABLE #date (date_id SMALLINT IDENTITY(1,1) PRIMARY KEY,
                    date_val DATE)
WHILE @Start<=@End
BEGIN
    INSERT INTO #date (date_val)
    SELECT @Start
    SET @Start=DATEADD(DD,1,@Start)
END

IF OBJECT_ID('tempdb..#roomInventory') IS NOT NULL
    DROP TABLE #roomInventory
CREATE TABLE #roomInventory (RoomType VARCHAR(5), Property VARCHAR(5), Inventory INT)
INSERT INTO #roomInventory (RoomType, Property,Inventory)
VALUES ('SUP','JS',20),
       ('DLX','JS',15),
       ('FAS','FB',6)

SELECT date_val, RoomType, Property, Inventory
FROM #date 
CROSS APPLY #roomInventory
ORDER BY date_val, RoomType

score 2 · Answer 3 · answered Jun 17 '16 at 17:11

2

Another option is to use your CalendarTable and

Select B.Date,RoomType,Property,Inventory 
 From  RoomDefTable A
 Cross Join CalendarTable B
 Where B.Date between '2016-01-01' and '2016-12-31'
 Order By B.Date

answered Jun 17 '16 at 17:11

John Cappelletti

79,615
7
44
66

Thanks. I like the simplicity in your solution! – user3115933 Jun 17 '16 at 19:30

What is the T-SQL syntax to fill the columns of a table in my database with the same set of data with continuous dates?

3 Answers3