Solved

Populate table with dates.

Posted on 2011-02-14
5
504 Views
Last Modified: 2012-05-11
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


0
Comment
Question by:koossa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 7

Accepted Solution

by:
mkobrin earned 500 total points
ID: 34886553
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
 
LVL 11

Expert Comment

by:rajvja
ID: 34886574
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34886578
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
 
LVL 10

Expert Comment

by:John Claes
ID: 34886600
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
 
LVL 18

Expert Comment

by:deighton
ID: 34886803
;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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question