CODEWITHSUNDEEP
sqloracle-database
Scott77
Scott77

Reputation: 21

Query multiple tables at once based on common table name in Oracle through SQL or PL/SQL

I have a requirement to fetch data from all the tables based on common table name pattern every week.Basically my requirement is to merge all the data to form a single lookup table.

Example:

Table Names:

Department_20190101
Department_20190109
Department_20190122
Department_20190129

I have to fetch data from all the tables and have to create a single lookup table. Is there a simple way to do this other than by going through iteration in PL/SQL by getting the table names with the help of ALL_TABLES

Note:The Date part is not consistent.If i can achieve this requirement once then i can easily start inserting the data from the new table to the existing lookup(n+1)

Please share your suggestions.

Upvotes: 0

Views: 568

Answers (2)

Santonia
Santonia

Reputation: 27

Check here: Execute For Each Table in PLSQL there is nice example to resolve Your problem using pl/sql (pl/sql help You dinamicly grow up sql-query) .

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

If you have a very long list of tables, or you requirement is to aggergate the results from all tables e.g. starting with Department_ followed by a certain date or range of dates, then you might need dynamic SQL for this. For the exact example you did show in your question, a CTE with a union query might work:

WITH cte AS (
    SELECT * FROM Department_20190101 UNION ALL
    SELECT * FROM Department_20190109 UNION ALL
    SELECT * FROM Department_20190122 UNION ALL
    SELECT * FROM Department_20190129
)

And then use the CTE as:

SELECT *
FROM cte;

This assumes that all tables have identical structures. Also, as a side note, if this be the case, you might want to consider just having a single table with a date column to differentiate between the otherwise common data.

Upvotes: 1

Related Questions