How to UNION multiple databases as one source

Question

I have 40+ databases (linked servers) in my SSMS connection, each has the same schema, tables, columns but with unique data for each individual location. I'm looking for the best way to UNION them all together into a VIEW or Alias that would allow me to assign and ID based on the database/location and have a single source to query against essentially for future cross reference.

For example, I would like to be able to query against a single common table between the databases that has columns [order], [item], [amount] and add an additional column [location] based on the database the data is coming from within the query. Such as:

SELECT *
FROM [UnionTableSource]

Which shows results such as

Location | Order | Item | Amount
1        | 1234  | 567  | $500
2        | 1234  | 529  | $125
3        | 1279  | 529  | $125
3        | 1234  | 789  | $100

Answer 1

You can create a view that UNIONs the tables together, 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 query it as you would any view:

SELECT * 
FROM AllOrders 
WHERE Location = 2

Be aware that performance might be an issue if you have 40 databases in this list, though it may even perform quite well if the tables are extremely large as the workload is split across servers. This is basically poor-man's partitioning.