views:

255

answers:

3

Hi there

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

+1  A: 

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.

Randolpho
Really putting them in two differnt locations with the same name is the only solution or every query is dynamic sql which is just an accident waiting to happen.
HLGEM
+8  A: 

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.

Jose Basilio
This is the approach I use too, but I use the nomenclature of AdventureWorksProductionProduct (stuff the entire qualified name into synonym name). You use the canonical db name for the name rather than the physical name, so the full synonym is meaningful regardless of physical db referenced.
+2  A: 

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:

http://stackoverflow.com/questions/869073/what-are-the-pros-cons-of-using-a-synonym-vs-a-view

richardtallent