MsTiggy MsTiggy - 4 months ago 7
SQL Question

Simple way to join many tables with similar names in SSMS?

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(id, date)


Replacing the "merge" statement above with whatever is appropriate. Is such a thing possible?

Answer

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 using keyword:

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.