Add Missing Sequence Numbers to One Table according to The Other and Combine Two Tables #eg18

Problem description & analysis

There is a OLDTABLE in a database. Its RANK column contains continuous numbers beginning from 1, as shown below:

There is also a NEWTABLE in the database. Its RANK table contains discontinuous numbers that included in OLDTABLE’s RANK, as shown below:

We are trying to insert records of NEWTABLE into OLDTABLE according to the orders of RANK in the two tables and the record after the newly-inserted record has the rank RANK+1, as shown below:

Solution

Write the following script p1.dfx in esProc:

Explanation:

A1   Connect to the database named demo.

A2  Perform SQL and return the query result as a table sequence.

A3  Perform SQL and return the query result as a table sequence.

A4  Close database connection.

A5  Loop through A3 to insert the current record into A2 at the position corresponding to the current RANK value of A3.

A6  Assign corresponding sequence numbers to RANK in A2.

A7  Return result of A2.

Read How to Call an SPL SCript in Java to learn about the method of integrating the SPL script with a Java program.

Q & A Collection

https://stackoverflow.com/questions/64141189/combine-tables-based-on-fixed-rank