Database Reference
In-Depth Information
, Cast( Year( @DateInProcess ) as nVarchar(50) ) -- [Year]
)
-- Add a day and loop again
Set @DateInProcess=DateAdd( d, 1, @DateInProcess )
End
-- 2e) Add additional lookup values to DimDates
Set Identity_Insert [DWPubsSales].[dbo].[DimDates] On
Go
Insert Into [DWPubsSales].[dbo].[DimDates]
( [DateKey]
, [Date]
, [DateName]
, [Month]
, [MonthName]
, [Quarter]
, [QuarterName]
, [Year], [YearName] )
Select
[DateKey]=-1
, [Date]=Cast( '01/01/1900' as nVarchar(50) )
, [DateName]=Cast( 'Unknown Day' as nVarchar(50) )
, [Month]=-1
, [MonthName]=Cast( 'Unknown Month' as nVarchar(50) )
, [Quarter]=-1
, [QuarterName]=Cast( 'Unknown Quarter' as nVarchar(50) )
, [Year]=-1
, [YearName]=Cast( 'Unknown Year' as nVarchar(50) )
Union
Select
[DateKey]=-2
, [Date]=Cast( '01/01/1900' as nVarchar(50) )
, [DateName]=Cast( 'Corrupt Day' as nVarchar(50) )
, [Month]=-2
, [MonthName]=Cast( 'Corrupt Month' as nVarchar(50) )
, [Quarter]=-2
, [QuarterName]=Cast( 'Corrupt Quarter' as nVarchar(50) )
, [Year]=-2
, [YearName]=Cast( 'Corrupt Year' as nVarchar(50) )
Go
Set Identity_Insert [DWPubsSales].[dbo].[DimDates] Off
Go
-- 2f) Get source data from pubs.dbo.titles and
-- insert into DimTitles
Select
[TitleId]=Cast( isNull( [title_id], -1 ) as nvarchar(6) )
, [TitleName]=Cast( isNull( [title], 'Unknown' ) as nvarchar(100) )
, [TitleType]=Case Cast( isNull( [type], 'Unknown' ) as nvarchar(50) )
When 'business' Then N'Business'
When 'mod_cook' Then N'Modern Cooking'
