Incremental INSERT statement
-
I have a date table where I need to populate all days between 6/6/2079 12:00:00 AM (this date is actually a placeholder for early arriving dimensions) and 9/29/2007 12:00:00 AM. I have tried several different queries, but have not gotten one to work. The table name is DimDateLM. The date field is Date_Idx (smalldatetime). I want to take the max date EXCLUDING 6/6/2079, increment it by one (using dateadd I would assume) and insert the record. I want to continue writing records until the max date is 01/01/2032. Could anybody help me out? Thanks!
-
Your Request does not make sense, 6/6/2079, or 6/6/1979?
anyhooo
Try this.
set nocount on
create table #mydatetable (pk int identity, Mydaveval smalldatetime)
SELECT TOP 20000 nmbr_pk = IDENTITY(int, 1, 1)
INTO #t_Numbers
FROM sysobjects t1, sysobjects t2, sysobjects t3
insert into #mydatetable (Mydaveval)
select top 19202 dateadd(dd,nmbr_pk,'6/6/1979 12:00:00 AM')
from #t_Numbers
SELECT * FROM #mydatetable
drop table #t_Numbers
drop table #mydatetable
-
Why doesn't 6/6/2079 make sense? Our date table (which comes from our ERP) currently only has dates until September. The 6/6/2079 date ensures that situations such as orders being placed past September will make it into their respective table, regardless of the fact that a date does not really exist in the date table.
-
Try this:
declare @startdate smalldatetime
select @startdate = (Select max(Date_Idx) from dbo.DimDateLM where Date_Idx < '2079-06-06')
set @startdate = dateadd(dd, 1, @startdate)
while @startdate < '2032-01-01'
begin
insert into dbo.DimDateLM set
Date_Idx = @startdate
set @startdate = dateadd(dd, 1, @startdate)
end
-
Or, since we're using SQL 2005, we can use a CTE
;
WITH LotsOfDates(TheDate) AS (
SELECT CAST('2007/09/29' AS DATETIME) AS StartDate
UNION ALL
SELECT DATEADD(dd,1,TheDate) FROM LotsOfDates WHERE TheDate <'2032/01/01'
)
INSERT INTO dbo.DimDateLM (DateIdx)
SELECT TheDate FROM LotsOfDates
OPTION (MAXRECURSION 10000)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
In my view populating *ALL* values to Dim Time will degrade your cube processing and data viewing from cube. Also its a good practise for keeping a biggest date as a placeholder for holding the early arriving data. We can achieve both !
Just have only one entry for your early arriving data placeholder and have a seperate job / script scheduled for generating dates in Dim Tim for populating normal data.
My recommendation is to have your job generating 1 week future dates in every 3 days will be a good options.
Ofcouse this may or may not apply in your case.
FP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply