Scriptella - DB to DB ETL Script that uses rows from output to populate new table

Question

I need to develop an ETL script (that runs after another one) that reads rows from one database (DB1) and copies them to other one (DB2).

The problem is that the query that selects the rows from DB1 joins with rows in DB2, something like this:

SELECT db2.tableA.columnA, db1.tableA.columnA
    FROM db2.tableA
    LEFT OUTER JOIN db1.tableA
    ON db1.tableA.columnB = db2.tableA.columnB

I am having trouble solving this since query tag receives one connection-id

Thanks!

Answer 1

It's hard for me to judge the business sense of the particular join, but I believe that the easiest approach from the implementation standpoint would be to create a temporary table in the db2, which is a copy of db1.tableA. Example:

<connection id="db1" ....>
<connection id="db2" ....>

<script connection-id="db2">
    CREATE TABLE tableAFromDb1 (
           ....
    );
</script>
<query connection-id="db1">
   <!-- Copy all rows from db1 to db2 -->
   SELECT columnA, columnB FROM db1.tableA
   <script connection-id="db2">
        INSERT INTO db2.tableAFromDb1 (columnA, columnB) VALUES (?1, ?2);
   <script>
</query>
<!-- Produce the outer join if necessary or directly manipulate the tableAFromDb1 dataset --    >
<query connection-id="db2">
    SELECT columnA, columnA
        FROM tableA LEFT OUTER JOIN
        tableAFromDb1 ON tableAFromDb1.columnB = tableA.columnB

    <script>
       TODO Insert the result somewhere
    </script>
</query>