Skip navigation
ACS LLC
ACS LLC
Currently Being Moderated

Inserting large amount of records in loop

Oct 5, 2011 6:10 PM

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>

602 Views   7 Replies   Latest reply: ACS LLC, Oct 6, 2011 12:13 PM
 
Replies
  • mack_
    Currently Being Moderated
    1. mack_,
    Oct 6, 2011 2:52 AM   in reply to ACS LLC
    Report

    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

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    2. Dan Bracuk,
    Oct 6, 2011 5:39 AM   in reply to mack_
    Report

    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.

     
    |
    Mark as:
  • ACS LLC
    Currently Being Moderated
    3. ACS LLC,
    Oct 6, 2011 8:08 AM   in reply to mack_
    Report

    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

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    4. Dan Bracuk,
    Oct 6, 2011 9:37 AM   in reply to ACS LLC
    Report

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

     
    |
    Mark as:
  • ACS LLC
    Currently Being Moderated
    5. ACS LLC,
    Oct 6, 2011 10:50 AM   in reply to Dan Bracuk
    Report

    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

     
    |
    Mark as:
  • Avatar
    Currently Being Moderated
    6. Avatar,
    Oct 6, 2011 12:06 PM   in reply to ACS LLC
    Report

    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>

     
    |
    Mark as:
  • ACS LLC
    Currently Being Moderated
    7. ACS LLC,
    Oct 6, 2011 12:13 PM   in reply to Avatar
    Report

    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

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points