I have many tables that are all named similarly (like "table1" "table2" "table3" etc.) and I need to use all of them in a query. They all contain the same two variables ("ID" and "date") that they are joined on.
There are at least 25 tables of this sort and I have read-only access to the database so I can't combine them or even create a view that would do so.
My question is: Is there a simple shortcut I can use to join all these tables? If this were SAS I would create a macro, but I'm using Microsoft SQL Server Management Studio 2012.
Instead of having to do this:
select * from table1 a join table2 b on a.id=b.id and a.date=b.date join table3 c on b.id=c.id and b.date=c.date join .... join .... join table25 y on x.id=y.id and x.date=y.date
I'd like to do something like:
select * from merge(table1 - table25) using(name, date)
Replacing the "merge" statement above with whatever is appropriate. Is such a thing possible?