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
I have tried various recipes including
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
SELECT COLUMNDATA FROM TABLE_FIRST
SELECT COLUMNDATA FROM TABLE_SECOND
UNION . . .