What is the best approach for joining two tables from different databases?

Question

What is the best approach for joining two tables from different databases? In my situation, I have a development database that has a postfix such as _DEV, while in production there is _PROD.

The problem is that if I join these two tables, I need to reference the full database name, such as DB1_DEV.dbo.table1 INNER JOIN DB2_DEV.dbo.table100

Work well, but if you want to move it into production, it will be a night's sleep, because I have to change them.

thanks

Answer 1

You can use Synonyms to simplify your queries. For instance:

-- Create a synonym for the Product table in AdventureWorks. USE tempdb; GO CREATE SYNONYM MyProduct FOR AdventureWorks.Production.Product; GO -- Query the Product table by using the synonym. USE tempdb; GO SELECT ProductID, Name FROM MyProduct WHERE ProductID < 5; GO 

EDIT:. You can define synonyms for the tables in question. Use a synonym instead of a full name anywhere you query tables.

When deployed to production, all you have to do is change the synonym.

Answer 2

Depending on your situation, SYNONYM might be the best answer or maybe VIEW.

VIEW example:

 CREATE VIEW table1 AS SELECT * FROM DB1_DEV.dbo.table1 

Later, when you go to PROD:

 ALTER VIEW table1 AS SELECT * FROM DB1_PROD.dbo.table1 

As is the case with SYNONYM, the update magically captures all queries that refer to simply "table1".

Here is a discussion explaining the differences between synonyms and perceptions:

What are the pros and cons of using synonyms versus submission?

Answer 3

Here's a suggestion: move your Dev and Prod databases to different server clusters with the same name.

If you cannot or will not do this, I suggest you find a way to parameterize your database names in your queries.

Answer 4

You must store your schema name somewhere in the database. Get this schema name and build dynamic sql. and use the oracle DBMS_SQL.EXECUTE () internal package to execute your select query.

Then, when you go to PROD, you don't need to change anything.