You are quite welcome... :) -----Original Message----- From: Torrent Girl [mailto:[email protected]] Sent: Monday, June 20, 2011 9:40 AM To: cf-talk Subject: Re: Retrieve data in groups
>If you want to update hundreds of thousands of records you might consider >NOT using CF to do it. It's not always the best choice for really big import >jobs. > >However - to answer your question... in the first case: > >SELECT TOP 100000 ... > > >You would need something in the where clause to help you get the data in >groups. For example, if you had an identity filed you could have... > >SELCT TOP 100000 * FROM BLAH >WHERE myID > 0 >Order by myID ASC > >... then find the max ID in the next query.... as in ... > >SELCT TOP 100000 * FROM BLAH >WHERE myID > #previousquery['myID'][previousquery.recordcount]# >Order by myID ASC > > >... and so on... > >In the SECOND exmpale (using max rows) you would simply select ALL the rows >in the query... > >SELECT * FROM blah > > >The use max rows and start rows to define the length of your loops > ><cfoutput startrow="1" maxrows="10000"> > ></cfouput> > >Next loop... > ><cfoutput startrow="100001" maxrows = "100000"> > > >... and so on till you are through the query.... > > >To reiterate my warning though.... dealing with very very large updates >using CF may be a poor choice for a number of performance reasons :) > >-Mark > > ><[email protected]>wrote: > >Thanks but what about the next 100000. I need to update all of the records. Thanks Mark. this is exactly what I was looking for. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345437 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
