You can insert all records in a single query, something like this:
insert into table (field1, field2, ...)
select 'field1value1', 'field2value', ...
select 'field1value2', 'field2value2', ...
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.
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
You currently have your query inside a loop. You can also put loops inside queries to achieve the syntax mack_ suggested.
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.
You can have SQL Server do the work, less database connections:
<CFQUERY NAME="InsertDate" DATASOURCE="#datasource#">
DECLARE @Start int,
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)
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
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