FlowHeater - The Data Import / Export Specialist
See also the general examples available, which may answer your question: Hello World 1 / 2 / 3 , format numbers as character strings , simple If-Then-Else , complex If-Then-ElseIf , filtering and grouping data , consolidate grouped values

Append Only New Records

  • Niel Bullock
  • Topic Author
  • Offline
  • New Member
  • New Member
More
11 years 4 months ago #2158 by Niel Bullock
Append Only New Records was created by Niel Bullock
I have a table with over 550K records and would like to bring over only the new ones. The table has an auto number field so I only need records where Read.NextRecord > Write.NextRecord.

Sounds easy but I am just not sure how work this in FlowHeater

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

More
11 years 4 months ago #2159 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Append Only New Records
Hi Niel,

Usually you just switch on then "Insert data" and "Ignore existing records" options on the WRITE side. With this FlowHeater decide by the primary key on the WRITE side if the record exits.

Note: It´s necessary to connect the primary key fields from the READ side to the WRITE side.

In your case the "auto number field" can be a problem! Please post your table schema (READ and WRITE) and I could make a little example for you.

The problem with this method is that FlowHeater must check all 550k records on the WRITE side for existing! This can be take a while.

Forecast for the next Version 2.1.1 coming soon in October) Here you can use dynamical filters like this.
  1. Determinate the last ID in a previous SQL statement
  2. Use this id in the SQL select statement on the READ side like this
    select * from your_table where id > %ID from WRITE$ side%

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.

  • Niel Bullock
  • Topic Author
  • Offline
  • New Member
  • New Member
More
11 years 3 months ago #2164 by Niel Bullock
Replied by Niel Bullock on topic Re:Append Only New Records
Has this enhancement been setup in the new version?

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

More
11 years 3 months ago #2165 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Append Only New Records
Hi Niel,

Yes this feature is released with the current version 2.2.1. There’s only a short time gap between new versions and the online help translation! Please excuse the delay.

First you have to define a parameter $MAX_ID$. You can find the parameter configuration dialog in the menu Edit->Parameters.

In this parameter the SQL Heater (new in version 2.2.1) stores the result from the SQL statement

Select max(id) from t_import

Note: The SQL Heater runs the SQL statement before the definition starts!

On the READ side you can now use this parameter to build a select SQL statement like this

Select * from t_export where id > $MAX_ID$

I’ve made a brief example; please have a look to the attachment.

Attachment dynamic_sql_filter.zip not found


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.
Attachments:

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

  • Niel Bullock
  • Topic Author
  • Offline
  • New Member
  • New Member
More
11 years 3 months ago #2166 by Niel Bullock
Replied by Niel Bullock on topic Re:Append Only New Records
This works perfectly, Thanks!

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

Time to create page: 0.346 seconds

other Languages

de

FlowHeater Home

de en

Imprint/Contact

Privacy Statement

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

Follow us on

twitter  facebook

YouTube

 de en