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

      etc.

       

      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!