7 Replies Latest reply: Feb 17, 2017 6:35 AM by Bill Markham RSS

    Multiple Similar Databases - Load/Join/Concatenate?

    Paul Senior

      I am loading data from 4 similar databases, each representing a different company. I have added a Company ID to an empty field in the LOAD script.


      The problem lies with joining tables in each database.


      The LOAD script runs as follows:


      CONNECT Database 1

      LOAD Table 1 Fields

      SQL SELECT Table 1 Fields

      JOIN LOAD Table 2 Fields

      SQL SELECT Table 2 Fields

      LOAD Table 3 Fields

      SQL SELECT Table 3 Fields

      LEFT JOIN LOAD Table 4 Fields

      SQL SELECT Table 4 Fields

      This works fine and the data is how I expected. So, I copied the LOAD script and added a similar one for the next database:

      CONNECT Database 2

      LOAD Table 1 Fields

      SQL SELECT Table 1 Fields



      This brings multiple synthetic keys and the data is messed up.

      I've also tried connecting to Database 1, loading Table 1, then connecting to Database 2, concatenating Table 1 etc. but I end up with similar problems.


      I guess what I need to do is concatenate the tables first and then make the joins later? Is this possible? Or I am barking up the wrong tree?


      Thanks for your help!