What is best approach of joining 2 tables from different Database?

Question

What is best approach of joining 2 tables from different Database? In my situation, I have a development database that has postfix such as _DEV while on the production has _PROD.

The issue is that if I join these 2 tables I have to reference a full database name such as DB1_DEV.dbo.table1 INNER JOIN DB2_DEV.dbo.table100

Work well though but if you want to move this into production, it will be a nightmaire cause I have to change these.

Thanks

Answer 1

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

-- 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 the Synonyms for the tables in question. Use the Synonym instead of the full name in any place where you query the tables.

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

Answer 2

Depending on your situation, a SYNONYM may be the best answer, or possibly a VIEW.

Example with a VIEW:

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

Later, when you move to PROD:

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

Just like with a SYNONYM, the update magically fixes all queries referring to simply "table1".

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

What are the pros/cons of using a synonym vs. a view?

Answer 3

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

If you can't or won't do that, I suggest you find some way to parameterize your database names in your queries.