January 25, 2013 at 11:26 am
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
January 25, 2013 at 12:10 pm
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 )
SELECT QuarterID = 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 ;
January 25, 2013 at 12:58 pm
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.
January 25, 2013 at 5:46 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply