Importing multiple .db files to sybase using SQL Anywhere 16 and merging Tables

[Former Member]

I am kinda new to sybase/SQL programming, but I am having to work with 160 databases with .db files that are accessible through Sybase Central (64-bit). Each database has several tables related to different chemicals (160 different chemicals thus 160 databases). Tables in all databases have same fields (only the database names are different). Anyway, I was able to import individual .db files to sybase using SQL Anywhere 16 and do the relational databases stuffs (merging and stuffs that I knew how to do in access) within a single database. However, I am looking for a way to merge all similar tables from all databases and ran into a problem when I imported all databases individually and did:

SELECT * FROM Database1.Table1 UNION ALL SELECT * FROM Database2.Table1 UNION ALL …..(tables from other databases).

While doing the above, I just got only repeated rows for Database1.Table1(when I did union once - I got duplicates for Database1.Table1, union twice - triplicates for Database1.Table1, and so on)

So I was wondering if someone would guide me to

find a way to import all 160 .db files (from 160 databases) from my computer directory to Sybase central (all at once without having to import them 160 times) and to unionize all similar tables such that say, if I have individual tables for chemical name and molecular weight, I would like to be able to unionize all of them in one master chemical name and molecular weight table. Like I said, I am just learning this language because I now need it for my research and would appreciate any comments or advices.

Thanks a lot!

[MarkCulp]

The database component of the table specification database.user.table is ignored by SQL Anywhere. To access another database you need to use a proxy table. So what you will do is create a proxy table for each table in one of the other databases and then select from the proxy table - the server will then make the connection to the other database to get the table data.

See the documentation for more information.