18 Replies Latest reply: Jun 20, 2011 10:02 AM by Nagaian Krishnamoorthy RSS

    multiple databases with same tables

      i have multiple databases for different operating sites but all of them have the same tables and i do not have a column that says where the record is from since all of them reside in site-specific databases so there was no need in the first place...

       

      how do i join them together but still be able to differentiate which sites the records are from?

       

      thanks in advance...

        • multiple databases with same tables
          Nagaian Krishnamoorthy

          You may add a field to each table identifying the source site as follows:

           

          // Connect to site1 database

          OLEDB connect to connection-string-of-site1

          // load individual tables

          Table1:

          Load 'Site1' as Source, filed1, field2, ... from ....

           

          Table2:

          Load 'Site1' as Source, field1,field2, ... from ...

           

          ...

          // Connect to site2 database

          OLEDB connect to connection-string-of-site2

          Concatenate (Table1) Load 'Site2' as Source, field1, field2, ... from ...

          Concatenate (Table2) Load 'Site2' as Source, ...

          ...

           

          // Repeat the above procedure for all sites

           

          Depending on the architecture, you may have to tweak the script.

           

          Hope this helps.

            • Re: multiple databases with same tables

              hi kris thanks for your reply,

               

              i followed your steps but i seem to get additional tables in the end,

               

              when i do a select fields or new sheet object, the fields listed there are like this:

               

              tablename.fieldA

              tablename.fieldB

              tablename-1.fieldA

              tablename-1.fieldB

               

              why is this so, isn't it supposed to be just tablename instead of the additional tablename-1? did i do something wrong?

               

              also, i did the loading of tables from different sites in different tabs of the script editor. that can't be the problem right?

                • Re: multiple databases with same tables
                  Nick Gan

                  hey axon, try this...

                   

                  Table1:

                  Load 'Site1' as Source, filed1, field2, ... from ....

                   

                  CONCATENATE Load 'Site1' as Source, field1,field2, ... from ...

                  • Re: multiple databases with same tables
                    Nagaian Krishnamoorthy

                    I tried to load from four different tables of an Access database by disconnecting the database connection after each load (in order to simulate multiple sites) and I do not get multiple tablenames with suffixes generated by the load.

                     

                    I have attached my qvw file.

                      • Re: multiple databases with same tables

                        sorry kris i'm unable to open your qvw, i'm using the free personal edition...

                         

                        could you paste ur codes here?

                          • Re: multiple databases with same tables
                            Nagaian Krishnamoorthy

                            Here is the script that I used.

                             

                            ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test.accdb];

                            Table1:

                            LOAD *, 'Site1' as Source;

                            LOAD Field1, Field2, Field3, Field4;

                            SQL SELECT * FROM Site1;

                             

                             

                            ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test1.accdb];

                            Table2:

                            LOAD *, 'Site1' as Source;

                            LOAD FieldA, FieldB, FieldC, FieldD;

                            SQL SELECT * FROM Site1;

                             

                             

                            ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test.accdb];

                            Concatenate (Table1) LOAD *, 'Site2' as Source;

                            LOAD Field1, Field2, Field3, Field4;

                            SQL SELECT * FROM Site2;

                             

                             

                            ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test1.accdb];

                            Concatenate (Table2) LOAD *, 'Site2' as Source;

                            LOAD FieldA, FieldB, FieldC, FieldD;

                            SQL SELECT * FROM Site2;

                             

                             

                            ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test.accdb];

                            Concatenate (Table1) LOAD *, 'Site3' as Source;

                            LOAD Field1, Field2, Field3, Field4;

                            SQL SELECT * FROM Site3;

                             

                             

                            ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test1.accdb];

                            Concatenate (Table2) LOAD *, 'Site3' as Source;

                            LOAD FieldA, FieldB, FieldC, FieldD;

                            SQL SELECT * FROM Site3;

                             

                             

                            ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test.accdb];

                            Concatenate (Table1) LOAD *, 'Site4' as Source;

                            LOAD Field1, Field2, Field3, Field4;

                            SQL SELECT * FROM Site4;

                             

                             

                            ODBC CONNECT32 TO [MS Access Database;DBQ=C:\USERS\Kris\Documents\Test1.accdb];

                            Concatenate (Table2) LOAD *, 'Site4' as Source;

                            LOAD FieldA, FieldB, FieldC, FieldD;

                            SQL SELECT * FROM Site4;

                            DISCONNECT;

                             

                            As you can see, I have loaded Table1 and Table2, followed by Table1 and Table2 from the second site, etc.

                             

                            Hope this helps.