I'm in the process of rebuilding one of our sites and part of that means
rationalising data and correcting mistakes that were made in the original
build. I've got a chunk of data that needs to be extracted and transformed,
then inserted into a new table in a new database. I'm currently using CF to
do the heavy lifting and so my first question is whether CF is best for
this or if I should try to do this with SQL queries. If the answer is 2 can
anyone point me in the right direction as to how I would go about doing
this in native SQL? Or is there a more efficient way to do this in CF?
Here's what the code currently looks like. Bear in mind that the "citem"
table contains over 90k rows so we're talking about a lot of data here.
<!--- gets the old data the modX fields need merging into a new dbase
schema --->
<cfquery datasource="#oldDSN#" name="gtd">
select id, mod, mod2, mod3, mod4
from citem
where active = 1
</cfquery>
<!--- Start looping through items --->
<cfoutput query="gtd">
<!--- Get the new item ID from the new database --->
<cfquery datasource="#newDSN#" name="getNewItemID">
select id from items where oldid = '#gtd.id#'
</cfquery>
<!-- Merge the mod fields into a list --->
<cfset topicList = ''>
<cfset topicList = listappend("#topicList#",'#gtd.mod#')>
<cfset topicList = listappend("#topicList#",'#gtd.mod2#')>
<cfset topicList = listappend("#topicList#",'#gtd.mod3#')>
<cfset topicList = listappend("#topicList#",'#gtd.mod4#')>
<!--- Loop through the list --->
<cfloop list="#topicList#" index="t">
<!--- Assuming there's a value in the field do the insert into the new
database --->
<cfif trim(t) NEQ '' and trim(t) NEQ '0'>
<!-- Get the value for the new topicid field --->
<cfquery datasource="#newDSN#" name="getNewTopicID">
select id from topics where oldid = '#t#'
</cfquery>
<!--- Insert the data into the new topiclinks table that will link topic
IDs and item IDs --->
<cfquery datasource="#newDSN#">
insert into topiclinks
(topicid,itemid)
values
('#getNewTopicID.id#','#getNewItemID.id#')
</cfquery>
<!--- Increment a counter so I know how many rows were created --->
<cfset numTopics = numTopics + 1>
</cfif>
</cfloop>
</cfoutput>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:353439
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
