2

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?

MsTiggy
  • 179
  • 1
  • 10
  • Honestly, 25 is no sweat. But yes, setting an alias is a lifesaver. Also, copy and past is your friend. Especially if the joins are similar go back and adjust. – clifton_h Jul 29 '16 at 17:07
  • Yeah, it's totally do-able. I'm always looking for more efficient ways to code, though, and am curious if this is a thing that is possible or not. – MsTiggy Jul 29 '16 at 17:27
  • The trouble is...how often does this specific use case exist? Not too often, really. But if you can dream it, you could sell it. ;) – clifton_h Jul 29 '16 at 17:38
  • I suspect you probably want a union. Of course this is all a horrible practice but temp tables might be a workaround for you. You could conceivably write a loop to look at tables by name and populate the temp table. – shawnt00 Jul 29 '16 at 18:12
  • 1
    Pro tip: never ***ever*** design a database like this. – RBarryYoung Jul 29 '16 at 19:22
  • @RBarryYoung, no kidding! But we must work with what we're given. – MsTiggy Jul 29 '16 at 19:23
  • Actually I'm thinking maybe you wanted `intersect`. I don't quite understand why you would want 25 tables joined together horizontally. – shawnt00 Jul 29 '16 at 22:30

1 Answers1

-1

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.

sstan
  • 35,425
  • 6
  • 48
  • 66