Sharing multiple tables between databases

[rdavis]

I hope I am posting this in the right area. I have multiple databases on the same server that are exactly the same, but they are for different 'companies'. I need to share several tables for some companies. These tables must be able to be changed from each database too.

I though of creating a new database with these tables, but that would mean changing the code in my program.

I was also thinking of creating a new database with these tables, but creating views in the databases that would share thee tables to trick it to be a table (would that work?)

What is the best route to go to achieve this.

Thanks in advance

Rob

Rob

 

[mrdenny]

If this was my system, I'd probably setup a database which holds these tables, then use SQL Replication to replicate the data to all the other databases. Then when you update the values in the one table in the main database, the other values would all be changed either immediately or the next time the replication is scheduled to run.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[JayKusch]

To add to Mr Denny's fine solution (and basing my comment on your remark of " These tables must be able to be changed from each database too" ... I believe you would need to look into Merge replication.

May get a little tricky if you have to create your own conflict resolvers for data integrity.

Thanks

J. Kusch