Select data from multiple existing tables dynamically

Question

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

Fix your design. Don't store data within the table's name. You should have one table, with a column to denote the value of whatever '082020', '092020' represent. — Thom A
– Thom A ♦, Commented Oct 29, 2020 at 9:02
Why do you need to have multiple table in the first place ? You already have a date column in the table. Put all into one table and you can throw away T3 — Squirrel
– Squirrel, Commented Oct 29, 2020 at 9:06
@DaleK I tried to implement this solution through the cursor, Selecting data and inserting it into a temporary table. It works, but it's very slow — MrJack0209
– MrJack0209, Commented Oct 29, 2020 at 9:22
@Larnu Because about 5M records are collected for each month. and to make it easier to select data — MrJack0209
– MrJack0209, Commented Oct 29, 2020 at 9:24
"and to make it easier to select data " Then you should be able to do this operation easily, if it's so "easy" using this approach. ;) Clearly it isn't as if it was "easy" you wouldn't be asking this question. What is easier is adding WHERE YourVarcharColumn = '092020', not having 10's of tables. Otherwise, look into partitioning. The real solution is to fix the design. — Thom A
– Thom A ♦, Commented Oct 29, 2020 at 9:32

Gordon Linoff · Accepted Answer · 2020-10-29 11:17:44Z

1

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.

answered Oct 29, 2020 at 11:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MrJack0209 Over a year ago

Thanks! I came up with a solution: I created a stored procedure, and I pass two parameters to it: the period from and to. And I form a sql query in a loop

Collectives™ on Stack Overflow

Select data from multiple existing tables dynamically

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related