How to pull data with ETL stored procedure using SQL Server

Question

In my old database, there is a table Album which stores information about ID, AlbumName, Release_Date (e.g. 01/01/2017) etc.

I want to further break down the Release_Date into a time dimension table, so I create a DimDateAlbum table.

This is the time dimension table I have created.

CREATE TABLE [DimDateAlbum]
(
    [DateKey] INT PRIMARY KEY, 
    [Date] DATETIME NOT NULL,
    [Year] INT NOT NULL,
    [Quarter] TINYINT NOT NULL, 
    [QuarterName] VARCHAR(6) NOT NULL, -- January to March: First, April to 
        June: Second etc
    [Month] TINYINT NOT NULL,
    [MonthName] VARCHAR(9) NOT NULL, -- January, February etc
    [Day] TINYINT NOT NULL, -- Field holds day number of Month
    [DayofWeek] TINYINT NOT NULL, 
    [WeekName] VARCHAR(9) NOT NULL, -- Field displays 1: Monday, 2: Tuesday etc 
)  

As discussed below: I can insert Release_Date into time dimension table as [DateKey], however, how do I further break down the date into year, quarter, day etc.?

INSERT INTO DimDateAlbum
    SELECT 
        a.Release_Date AS [DateKey],
        CONVERT (char(8), a.Release_Date, 112) AS [DateKey],
        a.Release_Date AS [Date],
        DATEPART(YEAR, a.Release_Date) AS [Year], -- calendar year
        DATEPART(QQ, a.Release_Date) AS [Quarter], -- calendar quarter
        CASE (qq, a.Release_Date) 
           WHEN 1 THEN 'First' 
           WHEN 2 THEN 'Second' 
           WHEN 3 THEN 'Third' 
           WHEN 4 THEN 'Fourth' 
        END AS [QuarterName], 
        DATEPART(MONTH, a.Release_Date) AS [Month], -- month number of the year
        DATENAME(MM, a.Release_Date) AS [MonthName], -- month name
        DATEPART(DAY, a.Release_Date) AS [Day],  -- day number of the month
        DATEPART(DW, a.Release_Date) AS [DayofWeek], -- day number of week 
        CASE datepart(DW, a.Release_Date)  
           WHEN 1 THEN 'Monday' 
           WHEN 2 THEN 'Tuesday' 
           WHEN 3 THEN 'Wednesday' 
           WHEN 4 THEN 'Thursday' 
           WHEN 5 THEN 'Friday' 
           WHEN 6 THEN 'Saturday' 
           WHEN 7 THEN 'Sunday'
        END AS [WeekName]
    FROM 
        dbo.Album AS a  

This code does not work, any help on how to fix it? Thank you so much!

Answer 1

If I understood your correctly you want to populate DimDateAlbum table. I've edited a little bit your table( added identity constraint to avoid writing this field manually) and now it looks like this:

CREATE TABLE [DimDateAlbum]
(
    [DateKey] INT IDENTITY CONSTRAINT PK_DimDateAlbum_ID PRIMARY KEY, 
    [Date] DATETIME NOT NULL,
    [Year] INT NOT NULL,
    [Quarter] TINYINT NOT NULL, 
    [QuarterName] VARCHAR(50) NOT NULL, -- January to March: First, April to 
    [Month] TINYINT NOT NULL,
    [MonthName] VARCHAR(9) NOT NULL, -- January, February etc
    [Day] TINYINT NOT NULL, -- Field holds day number of Month
    [DayofWeek] TINYINT NOT NULL, 
    [WeekName] VARCHAR(50) NOT NULL, -- Field displays 1: Monday, 2: Tuesday etc 
) 

And now you can insert your data. I've added a test variable to insert one row, however it can be used for inserting from table:

INSERT INTO dbo.DimDateAlbum
(   
    DateKey, 
    Date,
    Year,
    Quarter,
    QuarterName,
    Month,
    MonthName,
    Day,
    DayofWeek,
    WeekName
)
SELECT     
         CAST(a.Release_Date AS DATETIME)
       , YEAR(CAST(a.Release_Date AS DATETIME)) --        
       ,  DATEPART(QUARTER, CAST(a.Release_Date AS DATETIME)) -- Quarter
       , CASE -- Quarter Name
            WHEN DATEPART(QUARTER, CAST(a.Release_Date AS DATETIME)) = 1 THEN 'January to March' -- Quarter Name
            WHEN DATEPART(QUARTER, CAST(a.Release_Date AS DATETIME)) = 2 THEN 'April to June' -- Quarter Name
            WHEN DATEPART(QUARTER, CAST(a.Release_Date AS DATETIME)) = 3 THEN 'July to September' -- Quarter Name
            WHEN DATEPART(QUARTER, CAST(a.Release_Date AS DATETIME)) = 4 THEN 'October to December' -- Quarter Name
        END
        , MONTH(CAST(a.Release_Date AS DATETIME)) -- Month number
        , DATENAME(MONTH, DATEADD( MONTH, MONTH(CAST(a.Release_Date AS DATETIME)), 0) - 1) -- Month name        
        , DAY(CAST(a.Release_Date AS DATETIME)) -- 6
        , DATEPART(dw, CAST(a.Release_Date AS DATETIME)) -- 5
        , DATENAME(dw, CAST(a.Release_Date AS DATETIME)) -- Thursday
FROM Album a

Work example:

DECLARE @FooDate VARCHAR(30) = '2018-12-06 12:10:51.727'
INSERT INTO dbo.DimDateAlbum
(   
    DateKey, 
    Date,
    Year,
    Quarter,
    QuarterName,
    Month,
    MonthName,
    Day,
    DayofWeek,
    WeekName
)
SELECT     
         CAST(@FooDate AS DATETIME)
       , YEAR(CAST(@FooDate AS DATETIME)) --        
       ,  DATEPART(QUARTER, CAST(@FooDate AS DATETIME)) -- Quarter
       , CASE -- Quarter Name
            WHEN DATEPART(QUARTER, CAST(@FooDate AS DATETIME)) = 1 THEN 'January to March' -- Quarter Name
            WHEN DATEPART(QUARTER, CAST(@FooDate AS DATETIME)) = 2 THEN 'April to June' -- Quarter Name
            WHEN DATEPART(QUARTER, CAST(@FooDate AS DATETIME)) = 3 THEN 'July to September' -- Quarter Name
            WHEN DATEPART(QUARTER, CAST(@FooDate AS DATETIME)) = 4 THEN 'October to December' -- Quarter Name
        END
        , MONTH(CAST(@FooDate AS DATETIME)) -- Month number
        , DATENAME(MONTH, DATEADD( MONTH, MONTH(CAST(@FooDate AS DATETIME)), 0) - 1) -- Month name
        , DAY(CAST(@FooDate AS DATETIME)) -- 6
        , DATEPART(dw, CAST(@FooDate AS DATETIME)) -- 5
        , DATENAME(dw, CAST(@FooDate AS DATETIME)) -- Thursday