Is attaching the database supported only by SQLite and MS SQL Server?
All other DBMSes can only have one database per connection?
Scenario:
Let's say there is an archived database with some historical data. The application needs to compare with the current data for the same period.
The database in use is not the two mentioned above.
How do people solve that?
EDIT:
The plain SQL supports the syntax of catalog.schema.table
which produces fully qualified table name to the query.
However I believe that in order to use that syntax I have to have a connection to the second database.
Its very easy if 2 databases (2 database files) are running on the same server. Then, in case of SQL Server, for example you connect to the server, issue use db1
and then just use the appropriate syntax.
However if those 2 databases are physically located on 2 different servers, I think I need to create a connection to the second database in order to use fully qualified name. Am I right? I think I am because otherwise the DB engine will not know anything about the second DB file(s).
Now, my understanding is that there should be only one connection in order to use the syntax as SELECT catalog1.schema1.table1, catalog2.schema2.table2 FROM catalog1.schema1, catalog2.schema2 WHERE ....;
. Am I right? I don't know for sure, as I'm not a DB administrator - just a software developer trying to work with different DBMSes.
Now in the answer below I was told that the way to have this one connection to 2 different DBMSes - for PostgreSQL and mySQL. I'm sure the same mechanism exists for Oracle/SAP/Sybase/DB2.
TIA for confirming or deniying my suspicions.