How do I take data from 1 table and send to 2 tables?

CloverDX Platform
1

What is the best way to select data from a table in SQL Server and send to 2 (or more) tables in Oracle? I have a simple graph. I have 2 input tables and 2 output tables. This works, but is it the best way? I tried playing around with Partition transformer, but I can’t make sense of it. I don’t think it is right anyway.

Related, how do I take data from multiple tables and send to 1 table?

Thanks,
Scott

2

Hi Scott,

You are correct, there is a better way to duplicate the feed - the component you are looking for is SimpleCopy; it duplicates the input feed to several outputs (all the outputs have the same data).

For the opposite direction, you need component SimpleGather or Concatenate. Simplegather is optimized for performance and takes records from all inputs and sends them to single output in no particular order (first come, first served manner which may differ between runs). Concatenate, on the other hand, output first all records from the first input, then all records from send input, etc; it preserves input ordering. Please note that all the inputs must have same metadata.

3

Hi Jan,

UDAMAN! Thanks so very much for your awesome support. I used the SimpleCopy successfully. I will try the other components you mentioned.

Thanks,
Scott

4

Hi Jan,

I am trying to work with SimpleGather and I am running into trouble. I have 2 InputTables and I want to take some info from 1 and some info from the other and insert into a single table.

Here is an example:

Table1 contains col1, col2, and col3.
Table2 contains col1, col4, and col5.

The join is by col1 and I want to take Table1.col2, Table1.col3, Table2.col4, and Table2.col5 and insert into 1 table in my output.

So my first DBInputTable has the select from table1. The second DBInputTable has the select from table2. I don’t know how to get the Metadata links to match for the input to SimpleGather. I can write the query in a Metadata that joins both tables, but then I would not need SimpleGather. I could link directly to my DBOutput table. I need a function in the tool to do this so we don’t have to write the joins. This is an oversimplilfied example, but it applies to what we need to do.

Thanks,
Scott

5

Hi Scott,

You are correct - you need to perform a join. There are two options to do that - in SQL query or in CloverETL.

For the CloverETL part, instead of using SimpleGather, you will need to use ExtHashJoin or ExtMergeJoin. For either of them, you need to provide join key and mapping from matched inputs to single output.