Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2009-06-08 : 17:36:27
|
| 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 (on different server).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.table100Work well though but if you want to move this into production, it will be a nightmaire cause I have to change these.Some people suggest using Synonyms (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). I don't quite understand on this?Or is there any other approach?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-08 : 18:22:31
|
A synonym replaces almost any fully qualified object name.Read Books Online about SYNONYMs.The SYNONYM can be named "Yak" and refer to a table in another database.select * from dbo.yak will be the same as select * from db1_dev.dbo.table1When you later port the code to production you only chance the CREATE SYNONYM statement to point to db1_prod.dbo.table1 instead.The code in SP will still be SELECT * FROM Yak
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
|
|
|
Best approach of joining 2 tables