loading data into QuarterDimension table

Question

loading data into QuarterDimension table

sql1411

Ten Centuries

Points: 1390
More actions
January 25, 2013 at 11:26 am

#273243

I've a dimension table dim_Quarter as below. Now I need to insert data into this table starting from Jan01,1980 - Jan01,2020.
Thanks in advance,
CREATE TABLE [dbo].[dim_Quarter](
[QuarterID] [int] NOT NULL,
[Quarter] [int] NOT NULL,
[QuarterStartDate] [date] NOT NULL,
[QuarterEndDate] [date] NOT NULL,
[RecordID] [bigint] IDENTITY(1,1) NOT NULL,
[RecordSource] [nvarchar](500) NULL,
[RecordCreatedDateTime] [datetime] NOT NULL,
[RecordUpdatedDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_Dim_Quarter] PRIMARY KEY CLUSTERED
(
[QuarterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[dim_Quarter] ADD CONSTRAINT [DF_Dim_Quarter_RecordSource] DEFAULT ('Unknown') FOR [RecordSource]
GO
ALTER TABLE [dbo].[dim_Quarter] ADD CONSTRAINT [DF_Dim_Quarter_RecordCreatedDateTime] DEFAULT (getdate()) FOR [RecordCreatedDateTime]
GO
ALTER TABLE [dbo].[dim_Quarter] ADD CONSTRAINT [DF_Dim_Quarter_RecordUpdatedDateTime] DEFAULT (getdate()) FOR [RecordUpdatedDateTime]
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

TRUNCATE TABLE dbo.dim_Quarter ;

-- The need for both QuarterID and RecordID is not clear, presumably the QuarterID sequence has a different base value

DECLARE @offset INT = 1000 ;

INSERT INTO dbo.dim_Quarter ( QuarterID, [Quarter], QuarterStartDate, QuarterEndDate )

SELECTQuarterID = number + @offset,

[Quarter] = (number % 4) + 1,

QuarterStartDate = DATEADD(quarter, number, '1980-01-01'),

QuarterEndDate = DATEADD(quarter, number, '1980-04-01') - 1

FROM master.dbo.spt_values

WHERE type = 'P' AND number BETWEEN 0 AND 160 ;

SELECT * FROM dbo.dim_Quarter ;

sql1411 Ten Centuries Points: 1390 More actions · Answer 2

Thanks Scott. Your script gave me what I needed. However, I've read in some forums that microsoft will probrably get rid of "master.dbo.spt_values". Just wondering if there are some other ways to populate the same table without using the "master.dbo.spt_values"...just in case for my own future reference.

Thanks again.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

There are many ways, look up articles on tally tables.

A small range like 0..160 is easy.

SELECT TOP 161 number = ROW_NUMBER() OVER (ORDER BY object_id) - 1

FROM master.sys.all_columns

Or create a common table expression that generates the integer sequence 0..9, and cross join it to add digits. For example, 0..49999 would require five digits, so it would need four cross joins.