I have 500+ tables all containing a column called COLUMNDATA
. The table names are like: TABLE_FIRST, TABLE_SECOND, ... ,TABLE_FIVE_HUNDREDTH
. What I am trying to get is a column containing all the unique entries from COLUMNDATA
across all of these tables. The problem is made a little more challenging because all tables in the DB contain this column, and I am not interested in data from the tables which aren't called TABLE_NUMBER
.
I have tried various recipes including UNION
and INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'TABLE%'
but to no avail.
If anyone has an idea that would be great! Otherwise I'll just stick with the 1000 line script I've got already. For now I have a nasty but gets-the-job-done
CREATE TABLE TAB_NAME
AS
SELECT COLUMNDATA FROM TABLE_FIRST
UNION
SELECT COLUMNDATA FROM TABLE_SECOND
UNION . . .
ad infinitum