Inserting large amount of records in loop

[ACS LLC]

I have a loop that goes from current date to the end of the year and inserts a record for each day up to the end of the year

The question is, is there a better way to do this without having to loop..insert...loop..insert so many times!

Here's the code -->

<!--- OFFSET FOR TIME DIFFERENCE ON SERVER TO LOCAL TIME --->

<CFSET timedifference="2">

<CFSET TestAffUID = "1234">

<CFSET StartDate = #Now()#>

<CFSET EndDate = "01/01/2012">

<CFSET NumberOfDays = #datediff('d',StartDate,EndDate)#>

<CFLOOP INDEX="DateCounter" FROM="0" TO="#NumberOfDays#">

<!--- CREATE THE DATE --->

<CFSET DateToAdd = DateAdd( "d", Datecounter, StartDate )>

<!--- INSERT THE DATE INTO DBASE --->

<CFQUERY NAME="InsertDate" DATASOURCE="#datasource#">

INSERT INTO impressions

(

imp_aff_uid,

imp_date,

imp_counter

)

VALUES

(

#TestAffUID#,

#createodbcdate(dateadd('h',timedifference,DateToAdd))#,

0

)

</CFQUERY>

</CFLOOP>

[mack_]

You can insert all records in a single query, something like this:

insert into table (field1, field2, ...)

select 'field1value1', 'field2value', ...

union all

select 'field1value2', 'field2value2', ...

union all

...

--

Mack

[Dan_Bracuk]

If you go with mack_'s method, you will have to do something to escape the extra "union" keyword generated in your loop.

If you have records from last year, you can use and sql dateadd function to do everything in one query.  If you go with this approach, you will have to figure out what to do about leap years.

[ACS LLC]

I'm not sure how that would fit in? The values (dates) that I am creating are created within the loop on the fly, basically taking the current date, and then just incrementing by one, creating the next date until a specified date is reached, in this case the end of the year, so I guess there is no escaping the loop?

I could use the loop to create a LIST of date ...DatesToInsert (10/05/2011,10/06.2011..etc..) and then I just need a way to insert the DatesToInsert in one shot

Thanks

Mark

[Dan_Bracuk]

You currently have your query inside a loop.  You can also put loops inside queries to achieve the syntax mack_ suggested.

[ACS LLC]

I see.. although I'm not sure of the format, do you think you could post a typical, simple example that might give me a better understanding so I can then adopt to my use.

Thanks

Mark

[Avatar]

You can have SQL Server do the work, less database connections:

<CFQUERY NAME="InsertDate" DATASOURCE="#datasource#">

DECLARE @Start int,

        @End int,

        @NextYear varchar(20),

        @ImpDate smalldatetime

SET @Start = 0

SET @NextYear = CONVERT(varchar(4), year(DATEADD(yyyy, 1, GETDATE() ))) + '-01-01 00:00:00'

SET @End = (SELECT DATEDIFF (d , GETDATE(),  @NextYear ))

WHILE (@Start < @End)

BEGIN

    SET @ImpDate = (    SELECT DateAdd( hh, 2, DateAdd( d, @Start, getDate() ))    ) /*this is what you intended with the #createodbcdate(dateadd('h',timedifference,DateToAdd))# except that createodbcdate() does not capture the time, you really need createodbcdatetime() */

    /* SET @ImpDate = (    SELECT DateAdd( d, @Start, CONVERT(date, getDate(), 101) )    ) --if you want the time to be 00:00:00 */

    /* Print(@ImpDate) */

    INSERT INTO impressions ([imp_aff_uid], [imp_date], [imp_counter])

    VALUES (1234, @ImpDate, 0)

    SET @Start = @Start +1

END

</CFQUERY>

[ACS LLC]

I have the odbcdate tied to a variable that allows me to change the date because I'm on Eastern time, but the server is on a different time zone and I wanted it all in US Eastern

Unfortunately I'm not at the level you are at with SQL, I have a fair idea of what you did, but I'm a little uncomfortable with that level of code, it looks almost like a stored proc which I struggle with.

I could put a CFLOOP inside the query, but not sure how I would write the query in such a way that it can also insert all of the rows within that loop

Mark