StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POParse & Compare Data using Coldfusion & MySQL
text
Body
copied!<p>First, I'll explain what I need to do, then how I <em>think</em> I can achieve it. My current plan seems very inefficient in theory, so my question is whether there is a better way of accomplishing it.</p> <p>I have 2 Tables - lets call them 'Products' and 'Products_Temp', both are identical. I need to download a large number of files (XML or XLS) which contain product details (stock, pricing etc) from suppliers. These are then parsed into the Products_Temp table. Right now, I plan to use CF Scheduled Tasks to handle the downloading, and Navicat to do the actual parsing - I'm happy enough this is adequate and efficient enough.</p> <p>The next step is where I'm struggling - once the file has been downloaded and parsed, I need to look for any changes in the data. This will be compared against the Products table. If a change is found, then that row should be added or updated (if it should be removed, then I'll need to flag it rather than just delete it). Once all the data has been compared, the products_temp table should be emptied.</p> <p>I'm aware of methods to compare tables and sync them accordingly, however the issue I have is the fact I'll be handling multiple files from different sources. I had considered using only the products table and append/update, but I'm unsure how I could manage the 'flag deleted' requirement.</p> <p>Right now, the only way I <em>know</em> I can make it work is to loop through the products_temp table, do various cfquerys and delete the row once complete. However, that seems incredibly inefficient, and given the fact we're likely to be dealing with hundreds of thousands of rows, unlikely to be effective if we update everything daily.</p> <p>Any pointers or advice on a better route would be appreciated!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload