Needing to partition multiple databases and tables

Question

Post reply

Needing to partition multiple databases and tables

Kris-155042

Hall of Fame

Points: 3460
More actions
January 13, 2008 at 3:39 pm

#202227

I have 3 databases that are structurally the same. They are on the same instance of SQL2005 SP2a. I need to be able to combine all the data into one view, eg I have 3 employee tables in 3 different databases which are set up the same just have different data in it. I know this is possible but not sure where to start. Can someone please help out with the easiest way to do this.
Thanks in advance
Kris
Thanks,
Kris

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

You can perform cross database joins/unions by fully qualifying the table names like

Select a,b,c

From database.schema.table

union all

Select d, e, f

From database.schema.table

etc...

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

If you have DB1, DB2, DB3, I'd recommend you create a view for each table.

In DB1, create view db2Employee as select * from db2.dbo.Employee.

Also, create view db3Employee as select * from db3.dbo.employee.

Then I'd create a view of those as

create view vEmployee

as

select * from Employee

union select * from db2Employee

union select * from db3Employee

This way if you need to add a new view you can do it without changing code and if the databases move to another instance, you just change the view for that table, not the application code.

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

Are your DBs hosting distinct data?

Do you need to be able to update your data?

Then go for Distributed Partitioned Views

http://technet.microsoft.com/en-us/library/ms188299.aspx

Best Regards,

Chris Büttner

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 4

It appears while the tables in all the 3 databases are called the same they don't always contain the same columns. There is around 10 tables in each database that I want to combine. So 30 all up. The most significant is the Employee table which is different in each Database.

Does UNION ALL still apply in this situation? Or is there a better solution?

Thanks,

Kris

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 5

With a UNION, it only matters that the data types of the columns you choose are the same. The column names that are returned in the first query in the union are the ones that are used by your client application.

UNION ALL is supposedly more efficient, because by default UNION only returns distinct values.

You can experiment by unioning just two of your tables in a view, then try to select from it and you will see the column naming and data type behavior.