Populate table with dates.

I have for example a name 'John' and a start date '1990-01-01' and an end date '2011-01-01'

My destination table is [Name], [Date]

How would I insert an entry for each day in the destination using a single query?.


Example

[NAME]      [DATE]
John          1900-01-01
John          1900-01-02
John          1900-01-03
John          1900-01-04
John          1900-01-05
....................................
....................................
....................................
John          2010-12-30
John          2010-12-31
John          2011-01-01


koossaAsked:
Who is Participating?
 
mkobrinCommented:
declare @date datetime
set @date = '1900-01-01'
while @date <= '2011-01-01'
BEGIN
insert into tableName(Name, Date) values('John', @date)
select @date = dateadd(dd, 1, @date )
END
0
 
rajvjaCommented:
0
 
Alpesh PatelAssistant ConsultantCommented:
Hi,

Use code block to insert date from start date to end date. or use cursor. Run till end date and insert data in table.
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
Koossa,

the easy way is to create a small loop to create an entry

I've made a small Code snippit for you .
You can use it to create a Stored procedure, or you can change it to run Once.

Regards
Poor beggar


create table #Tmp
(
	[Name]  varchar(255),
	[Date] datetime
)


declare @start datetime;
declare @end datetime;
declare @Name varchar(255);

set @start = '1990-01-01' 
set @end = '2011-01-01'
set @name = 'John'


declare @LoopCounter int
declare @loops int
set @loops = convert(int,@end - @start)
set @LoopCounter=0

while @LoopCounter<=@loops
begin
	insert into #Tmp select @Name, dateadd(day,@LoopCounter,@start) 
	set @LoopCounter = @LoopCounter +1 
end 

select * from #Tmp
drop table #Tmp

Open in new window

0
 
deightonprogCommented:
;WITH CTE
AS
(
	SELECT CAST('1900-01-01' AS DATETIME) AS GenDate 
	UNION ALL
	SELECT DATEADD(day,1,GENDATE) FROM CTE WHERE GENDATE < '2011-01-01'
)

SELECT * INTO DATETABLE FROM CTE
option (MAXRECURSION   0);			
	

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.