I want to use Union to union two different tables, but they are in different databases. An error message told me that I can’t use Union in this way. How can I do that? Both tables use the same schema.
You have to prefix the query with the database name to get access to the other database’s tables. If the schemas are identical, then they should union. Here’s a simple example of the prefix:
select au_id from pubs.dbo.authors UNIONselect customerid from northwind.dbo.invoices