December 13, 2007 at 1:30 am
Hi, I have a situation where i need to populate date data starting from 1/1/2000 00:00:00 for the next 10 years. so next record will be 1/2/2000 and so on. for each every record there will be a primary key assigned to it. can you tell me how to do it in SSIS.
December 13, 2007 at 6:13 am
For a pure SSIS solution, you can create a date variable and use a For Loop container. Each time you loop, insert a record into your table and increment the date.
However, there are hundreds of versions of doing this (it is really common for data warehousing) in T-SQL. Search around a bit on your favorite search engine and I am sure you will find a script you can run.
December 13, 2007 at 6:23 am
Ok, I thought about it for a minute and decided to see if I could do this with a recursive CTE because I have never seen it done that way before.
WITH MyDate (Mydate, Level)
AS
(
SELECT CONVERT(DATETIME,'1/1/2000') AS MyDate, 0 AS Level
UNION ALL
SELECT D.MyDate + 1 AS MyDate, Level+1 AS Level FROM MyDate D
WHERE D.MyDate <= '1/1/2010'
)
SELECT * FROM MyDate
OPTION (MAXRECURSION 30000)
December 13, 2007 at 10:51 pm
Thanks Micheal for the Query. it helped me a lot.thanks very much.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply