CODEWITHSUNDEEP
sql-serverdatesql-insert
user3115933
user3115933

Reputation: 4443

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?

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.

Upvotes: 1

Views: 78

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81960

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

Upvotes: 2

Dave C
Dave C

Reputation: 7392

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

Upvotes: 3

Code Different
Code Different

Reputation: 93161

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)

Upvotes: 4

Related Questions