Linking tables between databases on same server

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Linking tables between databases on same server

Author

Topic

RBT
Starting Member

4 Posts

Posted - 2007-08-29 : 00:59:35

Hi, I have 2 similar databases on the same SQL server (due to licencing and customisation issues for off the shelf software).

I would like to create a third database with site specific lookup tables to map data across to site codes, to do summary queries for data reporting, and to create a single point for data access. I can't modify the 2 databases.

I have created the third database and added the necessary tables, relationships, views, etc. But, how do I link to tables in the other databases on the same server so that I can do joins in a query/view ? I have SQL Server Enterprise manager.

Thanks.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-29 : 05:01:21

You can't create foreign key constraints across DBs (although you could enforce RI through triggers). You just need to add the DB name to the tables in your queries. I.e.

SELECT * 
FROM [DB1].[dbo].[Table1] AS tab1
JOIN [DB2].[dbo].[Table2] AS tab2
ON tab1.MyID = tab2.MyID

Mark

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 06:29:24

If you have "some" of your data in the third database how is it maintained?

Beware of the issues associated with restoring the database to a synchronized moment in time!!

Personally I would put the data in one of the databases, put the "maintenance" tools in that, and create a VIEW in the Second database pointing to the First database:

CREATE VIEW DB2.dbo.MyTable
AS
SELECT * -- Put a complete column list here!!
FROM DB1.dbo.MyTable

Note that this VIEW has the same name as the Table in the first database, it will then appear "identically" in both.

If you want/can use a different name that's fine too.

You still run the risk that a Restore of DB1 causes orphaned data in BD2 (i.e. if DB1 is restored to an earlier point-in-time that DB2, and newly added codes, now used in DB2, are not restored)

Kristen

RBT
Starting Member

4 Posts

Posted - 2007-08-30 : 21:33:54

Thanks for that. I had spent a bit of time googling, but nothing very useful came up, except for this site.

quote:
Originally posted by mwjdavidson

You can't create foreign key constraints across DBs (although you could enforce RI through triggers). You just need to add the DB name to the tables in your queries. I.e.
SELECT *
FROM [DB1].[dbo].[Table1] AS tab1
JOIN [DB2].[dbo].[Table2] AS tab2
ON tab1.MyID = tab2.MyID

Mark

RBT
Starting Member

4 Posts

Posted - 2007-08-30 : 21:44:00

Thanks for the help.

The data in my mtce database will be very static. It will be used to map Pant downtime events to cause codes. The mapping won't get changed much as it is defined by business rules. Simple grouping queries can then be run against views in this table to get summary data for monthly and yearly metrics for reporting (amongst other things).

The data collection gets done in the off the shelf databases.

Already tried it out now, and it works a treat.

Thanks, RBT.

quote:
Originally posted by Kristen

If you have "some" of your data in the third database how is it maintained?

Beware of the issues associated with restoring the database to a synchronized moment in time!!

Personally I would put the data in one of the databases, put the "maintenance" tools in that, and create a VIEW in the Second database pointing to the First database:

CREATE VIEW DB2.dbo.MyTable
AS
SELECT * -- Put a complete column list here!!
FROM DB1.dbo.MyTable

Note that this VIEW has the same name as the Table in the first database, it will then appear "identically" in both.

If you want/can use a different name that's fine too.

You still run the risk that a Restore of DB1 causes orphaned data in BD2 (i.e. if DB1 is restored to an earlier point-in-time that DB2, and newly added codes, now used in DB2, are not restored)

Kristen