How UNION multiple databases as one source
I have 40+ databases (linked servers) in my SSMS connection, each with the same schema, tables, columns, but with unique data for each distinct location. I'm looking for a better way to UNION them all together into a view or alias that would allow me to assign both IDs based on databases / location and has a single source to query for essentially future cross-referencing.
For example, I would like to be able to query a single shared table between databases, has columns [order], [item], [count] and add an additional column [location] based on the database that comes from the query. For example:
SELECT *
FROM [UnionTableSource]
Which shows results like
Location | Order | Item | Amount
1 | 1234 | 567 | $500
2 | 1234 | 529 | $125
3 | 1279 | 529 | $125
3 | 1234 | 789 | $100
source to share
You can create a view where UNION joins tables, for example:
CREATE VIEW AllOrders
AS
SELECT 1 AS Location, Order, Item, Amount FROM [Server1].[Database].dbo.Orders
UNION ALL
SELECT 2 AS Location, Order, Item, Amount FROM [Server2].[Database].dbo.Orders
UNION ALL
SELECT 3 AS Location, Order, Item, Amount FROM [Server3].[Database].dbo.Orders
--etc...
And you can request it like any kind:
SELECT *
FROM AllOrders
WHERE Location = 2
Be aware that performance can be an issue if you have 40 databases on this list, although it can even perform quite well if the tables are extremely large as the workload is spread across the servers. This is mainly separation from poor people.
source to share