0

I have a database which contains 100+ tables with data from sensors; one table for each sensor. And using 100+ union all is not good. So, is there a better way to collect data from all tables with maximum profit?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NisuSan
  • 197
  • 1
  • 13
  • 2
    You don't have much choice other than `union all`, so use it. – Gordon Linoff Jan 15 '18 at 13:57
  • 2
    Any particular reason why you have one table per sensor? – Professor Abronsius Jan 15 '18 at 13:57
  • 2
    Why on earth do you have one table per sensor? Why can't you use UNION ALL? Are you able to change anything about the structure? Could you, even temporarily, create a holding table to insert all your data into prior to use? – MatBailie Jan 15 '18 at 14:00
  • I did not create this structure, and I can not change it. So, if only `union all` is best way, I will use it. – NisuSan Jan 15 '18 at 14:07
  • 4
    Create a view with "union all" inside and then use it as a table – Denis Rubashkin Jan 15 '18 at 14:31
  • 1
    Presuming that the tables have a similar naming convention, you could write a stored procedure to select from sys.tables where name matches pattern, insert those into a temp table, and select from that. – Kyle B. Jan 15 '18 at 15:11

1 Answers1

0

As other commenters have pointed out, union all (optionally packed into a view) is the only solution here.

However, it is better to acquaint yourself with certain caveats and limitations that SQL Server imposes on queries with numerous union statements. With luck, your company might eventually hit one of those limits, which might force the management to reconsider the approach to the database design.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33