Execute process 100 by 100 to insert values in DB

Highlighted
Five Stars

Execute process 100 by 100 to insert values in DB

Hello people,

 

I´m with some trouble in my design and development in a way to execute the process i leave in attachment.

So heres the thing, i have a table that contains 1000 rows with a flag so i can say how many of those i will migrate.

 

Problem:

How can i loop the process 100 by 100 with the flag beeing updated at start of each loop? So at the start, my process should update the first 100 rows ,and migrate information for the first 100 then should deativate the first 100 and go for the next 100 and migrate until reaches 700. how to do this ?

 

DB - SQL Server.

Talend version - last version.

 

Regards,

Nine Stars

Re: Execute process 100 by 100 to insert values in DB

I think you need a key or unique index in your table

You first select records where unique_column between start and start+100

Then you update your flag and do all your processing

To update a table : tDBOutput, then you choose the option "UPDATE"
Regards
DGM
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Employee

Re: Execute process 100 by 100 to insert values in DB

Hi,

 

    Usage of the primary key (if its not present, a unique index) is the best way to update records when you are processing the records in loops. 

 

   But the flip side of the loop is that it will take more time to process since the data will be taken as batches. So the batch size will be a crucial element to optimize the flow throughput. I would recommend to increase the batch size as much as possible to reduce the number of iterations (which means less processing time).

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

 

Five Stars

Re: Execute process 100 by 100 to insert values in DB

Hi,

 

You guys are far too kind ty for response.

 

What i came up with was this i leave in attachment.

What i would like to know is how can i do that with a loop instead of multiple DB? The TRun JOB always call same subjob.

 

 

Regards,

Employee

Re: Execute process 100 by 100 to insert values in DB

Hi,

 

    Lets go back to the basics. Could you please share the rationale for doing this process as multiple batches instead of a single batch?

 

    If you increase the memory of the job, you should be able to do the same process as a single batch right?

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog