FlowHeater Demo Video
 
FlowHeater - The Data Import / Export Specialist
 
Welcome, Guest
Username: Password: Remember me
See also the existing examples of using the Excel Adapter , which may answer your question: CSV export with grouping
  • Page:
  • 1

TOPIC: SAVE SELECTED DATA TO NEW TABLE BEFORE UPDATE

SAVE SELECTED DATA TO NEW TABLE BEFORE UPDATE 6 months 2 weeks ago #3506

  • Frank Murphy
  • Frank Murphy's Avatar Topic Author
  • Offline
  • Posts: 2
We use our licensed Flowheater Designer very successfully on a daily basis to insertinto and update our database (currently 5.1 million plus rows).The updates are transaction driven and in two of these - NAMECHANGES and ADDRESS CHANGES – it would be very useful to be able to capture the
old data in two other tables immediately before the insert process.  Is this possible with Flowheater and, if so, whatwould be the recommended approach?  Volumes are modest (Names about 300 daily and Addresses 3,000 daily outof total update rows of 60K – 100K) so using LOOKUP seems like a sledgehammer
to open a nut; the input source is Excel (where we refine the raw input ready
for insert).Were we doing this work manually from the front end, then forNAMES CHANGES we would be using something along the lines of
$tblEvents = $dal->Table("Old_Name"); Thistable would have new Auto Increment PRIMARY KEY
$tblEvents->Value["Comp_Number"]=$oldvalues["Comp_Number"];(VARCHAR and original table PRIMARY KEY)
$tblEvents->Value["Comp_Name"]=$oldvalues["Comp_Name"];(VARCHAR)
$tblEvents->Value["Postcode"]=$oldvalues["Postcode"];
$tblEvents->Add();
but manual process times prevent this.  The ADDRESS CHANGES has more fields but isthe principles are identical.

Please Log in or Create an account to join the conversation.

SAVE SELECTED DATA TO NEW TABLE BEFORE UPDATE 6 months 2 weeks ago #3507

Hi Frank,

Two solution are possible.

Fast solution)
In case of your database supports database triggers, you could define a “before update trigger”. In this trigger function, you copy the current content to another table before FlowHeater overrides this data with the new data.

Slow solution, with FlowHeater)
Because FlowHeater does not read the content of the current record before updating, you have the read the desired content in separate or tow processing step and check whether update or insert needed for this record.

Step 1) Read just the primary keys of your import/update source into a InMemory table .

Step 2) Read the Table content you wants to update and check whether the primary key exit in the InMemory table .  For this you have can use the SQL , Lookup or the String Replace Heater . Note this check is very fast! In case, the primary key doesn´t exits just filter out this record. In case you have a match write/insert the desired content into your new table.

Step 3) Update your content as usual

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in or Create an account to join the conversation.

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

SAVE SELECTED DATA TO NEW TABLE BEFORE UPDATE 6 months 2 weeks ago #3508

  • Frank Murphy
  • Frank Murphy's Avatar Topic Author
  • Offline
  • Posts: 2
Many thanks for your thoughts which are very helpful.  Our preferred route is to use FlowHeater to we will set up and try the Slow solution when time permits later this month.  We will let you know how it works out.

Thanks again for your help.

Please Log in or Create an account to join the conversation.

  • Page:
  • 1

other Languages

de

FlowHeater Home

de en

Imprint/Contact

Privacy Statement

Copyright © 2009-2019 by FlowHeater GmbH.
All rights reserved.

Follow us on

twitter  facebook

YouTube

 de en