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:
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 table25 y on x.id=y.id and x.date=y.date
from merge(table1 - table25) using(id, date)
As pointed out in the comments, the succinct syntax you are looking for doesn't exist.
The only way to shorten the SQL that takes advantage of the fact that the joining columns are all named the same would involve using the
select * from table1 a join table2 b using (id, date) join table3 c using (id, date) join .... join .... join table25 y using (id, date)
But sadly, even that won't work for you, because the
using keyword is not recognized in SQL Server. It does work in other popular databases though.