Skip to main content
simply more productive

The Data Specialist

Thanks to our intuitive transformation designer, you can convert data effortlessly to a variety of formats without knowing how to program. It’s not only efficient, it’s also easy to use.
Flowheater Download

Append Only New Records

  • Niel Bullock
  • Topic Author
  • Offline
  • User
  • User
More
12 years 6 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 to join the conversation.

More
12 years 6 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 to join the conversation.

  • Niel Bullock
  • Topic Author
  • Offline
  • User
  • User
More
12 years 5 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 to join the conversation.

More
12 years 5 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 to join the conversation.

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

Please Log in to join the conversation.

Time to create page: 0.332 seconds

FlowHeater - The Data Specialist

Efficient data integration and transformation with FlowHeater – the perfect solution for a seamless transfer of your data.

Rechtliches

Support & Contact

Kontaktinformation

Telefon:0951 / 99339792 E-Mail:This email address is being protected from spambots. You need JavaScript enabled to view it.

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