Say you have one production database and an archive database for your application. A quite common setup.
If you want to grab data from both tables in one single query, there are some ways to do this.
- you could create a view (good for more permanent use)
- you could create a temporary table, inserting data from both tables (good if you want to manipulate the data in some special way before presenting it)
- or you could do it with a quick UNION ALL statement (quick and easy)
select myColumn from Production..TableA (nolock) UNION ALL select myColumn from Archive..TableB (nolock)
You can of course also add an order by clause to the query.