0

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

  1. 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

  2. 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!

Ram Siwakoti
  • 1
  • 2

1 Answers1

0

I don't think you can connect to multiple DBs in one query with SQL Anywhere. Your query is just hitting the Table for the active DB connection.

From SAP: *

Both the SQL Anywhere personal database server and the network database server can manage many databases simultaneously. Each connection from an application must be to a single database, but an application can use separate connections to different databases, or a set of applications can work on different databases, all through the same database server.

* http://dcx.sap.com/sa160/en/saintro/multidb-application-architecture.html

But! you should be able to write a script to iteratively 'Output To' [append] a text file and then select everything from that DBF

--Cycle Connections
SELECT *
FROM Table;
OUTPUT TO 'Employees.txt' 
APPEND;
-- Next
input into my_Master_DB from my_DBF

http://dcx.sybase.com/1201/en/dbusage/load-s-4696303.html

EoinS
  • 5,405
  • 1
  • 19
  • 32
  • Thank you for your reply. Currently, for each chemical I have five different tables (T1,T2,T3,T4,T5). The table field formats are same for all 160 chemicals. So Should i write five different scripts to create five different master tables? Also, while mentioning "Table", should I include " Database1.Table"? Is 'Append' adding each new table from new database at the bottom of the earlier table? Also, for my_master_table, should I create a main table with all desired fields or can i use one of my database table as a master table? Thanks so much for your assistance, – Ram Siwakoti May 17 '16 at 02:10
  • Ram, append adds the data to the output file. It is typically used for exporting data, sometimes for db migrations. How many users do you have, how are people connecting to the data. How often do you need aggregated data? If you don't have access issues or don't have any issues with storing this data in one db that may be the best route. – EoinS May 17 '16 at 03:50
  • Honestly scripting can be insanely easy. If you are not comfortable writing a script you could honestly export all db names to text with a query and paste into excel and write one formula and drag down. If you want help writing we'll need for details on the tables etc. You can have 5 master tables or you could aggregate it depends on your needs – EoinS May 17 '16 at 03:57
  • 1
    These databases were created by a user many years ago and we recently had a handle of them for our research purposes. So he is still the owner of the original database. So my tables are indicated as thatolduser.table1. Once I have the master tables, I will just export them to excel or JMP and work on the analysis and have no need to touch them again. – Ram Siwakoti May 17 '16 at 05:02
  • Please let us know how the migration goes feel free to ask about saving time on the output script – EoinS May 17 '16 at 15:42
  • I am working on the script right now. Could you guide me how I can export all db names to text with a query. I am being able to transport just one at the moment. – Ram Siwakoti May 17 '16 at 15:48
  • select db_name( number ) from sa_db_list(); – EoinS May 17 '16 at 15:53
  • I got that here with some more information: http://sqlanywhere-forum.sap.com/questions/4901/sql-query-to-get-all-database-names – EoinS May 17 '16 at 15:53
  • Will do. Thanks for your help. I might come back with more questions. – Ram Siwakoti May 17 '16 at 16:01