7 Replies Latest reply on Oct 6, 2011 12:13 PM by ACS LLC

    Inserting large amount of records in loop

    ACS LLC Level 1

      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>

        • 1. Re: Inserting large amount of records in loop
          mack_ Level 3

          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

          • 2. Re: Inserting large amount of records in loop
            Dan Bracuk Level 5

            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.

            • 3. Re: Inserting large amount of records in loop
              ACS LLC Level 1

              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

              • 4. Re: Inserting large amount of records in loop
                Dan Bracuk Level 5

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

                • 5. Re: Inserting large amount of records in loop
                  ACS LLC Level 1

                  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

                  • 6. Re: Inserting large amount of records in loop
                    Avatar Level 1

                    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>

                    • 7. Re: Inserting large amount of records in loop
                      ACS LLC Level 1

                      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