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
Best Answer
Yes, you need a script but it doesn't have to be 1000 lines.
--make a table or global temporary table to put the results in
--add the primary key of PK_ID, are contents larger than 4000 characters? use a CLOB
--create a trigger on the table if the PK_ID is null add a sequence value
--then do a select distinct columndata from temp_column_data