CODEWITHSUNDEEP
sqlsql-servert-sql
MrJack0209
MrJack0209

Reputation: 51

Select data from multiple existing tables dynamically

I have tables "T1" in the database that are broken down by month of the form (table_082020, table_092020, table_102020). Each contains several million records.

+----+----------+-------+
| id | date     | value |
+----+----------+-------+
| 1  | 20200816 | abc   |
+----+----------+-------+
| 2  | 20200817 | xyz   |
+----+----------+-------+

+----+----------+-------+
| id | date     | value |
+----+----------+-------+
| 1  | 20200901 | cba   |
+----+----------+-------+
| 2  | 20200901 | zyx   |
+----+----------+-------+

There is a second table "T2" that stores a reference to the primary key of the first one and actually to the table itself only without the word "table_".

+------------+--------+--------+--------+--------+
| rec_number | period | field1 | field2 | field3 |
+------------+--------+--------+--------+--------+
| 777        | 092020 | aaa    | bbb    | ccc    |
+------------+--------+--------+--------+--------+
| 987        | 102020 | eee    | fff    | ggg    |
+------------+--------+--------+--------+--------+
| 123456     | 082020 | xxx    | yyy    | zzz    |
+------------+--------+--------+--------+--------+

There is also a third table "T3", which is the ratio of the period and the table name.

+--------+--------------+
| period | table_name   |
+--------+--------------+
| 082020 | table_082020 |
+--------+--------------+
| 092020 | table_092020 |
+--------+--------------+
| 102020 | table_102020 |
+--------+--------------+

Tell me how you can combine 3 tables to get dynamic data for several periods. For example: from 15082020 to 04092020, where the data will be located in different tables, respectively

Upvotes: 0

Views: 184

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

There really is no good reason for storing data in this format. It makes querying a nightmare.

If you cannot change the data format, then add a view each month that combines the data:

create view t as
    select '202010' as YYYYMM, t.*
    from table_102020
    union all
    select '202008' as YYYYMM, t.*
    from table_092020
    union all
    . . .;

For a once-a-month effort, you can spend 10 minutes writing the code and do so with a calendar reminder. Or, better yet, set up a job that uses dynamic SQL to generate the code and run this as a job after the underlying tables are using.

What should you be doing? Well, 5 million rows a months isn't actually that much data. But if you are concerned about it, you can use table partitioning to store the data by month. This can be a little tricky; for instance, the primary key needs to include the partitioning key.

Upvotes: 1

Related Questions