Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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.
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?
hey axon, try this...
Table1:
Load 'Site1' as Source, filed1, field2, ... from ....
CONCATENATE Load 'Site1' as Source, field1,field2, ... from ...
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.
sorry kris i'm unable to open your qvw, i'm using the free personal edition...
could you paste ur codes here?
but this will only work if i load table1 from siteA then table1 from siteB, one table at a time right?
yes, one table at a time by script, i think there's no way u can do parellel loading by script, unless, u use dummy qvw and then load into QVD, but again, because of CONCATENATE, it wont make much sense loading it together , i think. does the script work for u ?
i don't need parallel loading, just one time connections to each site's database, get all the site's database's tables, then connect to the 2nd site's database, get all the 2nd site's database's tables and concatenate their rows together...
if i have to do it table by table, i have to repeatedly connect to each site's database
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.