[dba-SQLServer] Filling Table with Range of Dates

actually, there is no loop in my suggestion...
here is what I mean... (displays the last 365 days)

SET NOCOUNT ON
CREATE TABLE #THE_TABLE(THE_ID SMALLINT IDENTITY(1,1), THE_VALUE CHAR(1));

INSERT INTO #THE_TABLE VALUES('X');
INSERT INTO #THE_TABLE SELECT THE_VALUE FROM #THE_TABLE;

INSERT INTO #THE_TABLE SELECT TOP 363 A.THE_VALUE FROM #THE_TABLE A, 
#THE_TABLE B, #THE_TABLE C, #THE_TABLE D, #THE_TABLE E, #THE_TABLE F, 
#THE_TABLE G , #THE_TABLE H , #THE_TABLE I
SELECT DATEADD(day,-the_id+1, convert(varchar(10),getdate(),101)) FROM 
#THE_TABLE
SET NOCOUNT OFF

Billy

>From: David Emerson <davide at dalyn.co.nz>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer] Filling Table with Range of Dates
>Date: Tue, 19 Oct 2004 20:07:50 +1300
>
>That still means that I need to loop to run the insert statements.
>
>What I have done is as follows:
>
>         SET @MthDate = @FirstDate
>         WHILE @MthDate <= @LastDate
>                 BEGIN
>                         INSERT INTO egasSQLbe.dbo.ttmpWholesaleMDQ 
>([Date])
>                         VALUES (@MthDate)
>                         SET @MthDate = DATEADD(day, 1, @MthDate)
>                 END
>
>
>I was looking to see if there was a simple way of running the insert as a 
>single statement without the loop.
>
>Mainly academic as what I have done works.
>
>David
>
>At 19/10/2004, you wrote:
>>how about..
>>
>>1. create a temporary table that contains identity column.
>>
>>2. insert one record into that temporary table.
>>
>>3. execute X number of insert statements with "select" as source of the 
>>table you just created (therefore, number of records will insert by two 
>>folds per insert execution).
>>
>>4. when you are satisified with number of records in temporary table, 
>>write a final insert statement containing dateadd function with identity 
>>column as one of the parameters.
>>
>>hth
>>Billy
>>
>>
>>>From: David Emerson <davide at dalyn.co.nz>
>>>Reply-To: dba-sqlserver at databaseadvisors.com
>>>To: dba-SQLServer at databaseadvisors.com
>>>Subject: [dba-SQLServer] Filling Table with Range of Dates
>>>Date: Tue, 19 Oct 2004 13:20:42 +1300
>>>
>>>SQL2000
>>>
>>>I have a table that I would like to fill in with a range of dates.  Is 
>>>there a simple insert statement that I can do this easily, or do I need 
>>>to write a loop and run a separate insert for each date?
>>>
>>>Regards
>>>
>>>David Emerson
>>>Dalyn Software Ltd
>>>25 Cunliffe St, Churton Park
>>>Wellington, New Zealand
>>>Ph/Fax (04) 478-7456
>>>Mobile 027-280-9348
>>>
>>>_______________________________________________
>>>dba-SQLServer mailing list
>>>dba-SQLServer at databaseadvisors.com
>>>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>>http://www.databaseadvisors.com
>>
>>
>>_______________________________________________
>>dba-SQLServer mailing list
>>dba-SQLServer at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>http://www.databaseadvisors.com
>>
>>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>