Hi guys - I'm new to the forums so I'm not sure where this query lives.
So I need to append/union maybe 60 tables. I realise that I can use union/union all to do this task. However as you know - this only works if all tables have the same structure (i.e. same number of fields and field names are identical).
The problem is that my tables are very wide and although they all share a lot of the same fields as each other - there are quite a lot of fields that are unique to certain tables.
Therefore union/union all will not work.
I understand that I can use dummy fields (or null fields) to match up table structure so a union/union all could be used - I really do not want to do this due to the amount of time it would take to make this work.
Is there another solution?
For those who know SAS - the following statement would work but is there an SQL alternative?
data newtable
set table1 table2 table3 .... table60
run;
Hope this is enough information - any help would be great.
First get the columns you want in the final table. Create the table
Do the columns in the source tables change?
Do the source tables change?
Find which columns are in which source tables. Then you can use one of two methods to add data to destination table.
If the tables/columns do not change you can create your union from there.
The second way is to use INSERT INTO with a check to see the record is unique.
If there large numbers of tables to UNION ALL and/or large numbers of columns and IF the matching columns in the various tables have identical names, I would query the System Tables to auto-generate the UNION statement.
The various columns do have identical names in the various tables.
An example is that Table1 has 50 columns and Table2 has 55 columns. And only 45 may be common in both. I need the final table to have 55 columns, with the 10 additional columns not in table1 having null values, and the 5 additional columns not in table2 having null values.
If it was just this example - I'd bite the bullet and add dummy fields in to create 2 tables of identical structure - and then union them.
However there are approx. 60 of these tables.
How could I query the system tables to auto-generate the union statement?
SELECT DISTINCT C.name
FROM sys.tables AS T
JOIN sys.columns AS C
ON C.object_id = T.object_id
WHERE T.name IN ('MyTable1, 'MyTable2', ...)
will give you a list of all possible column names.
You could then modify that query to generate a
SELECT Col1, Col2, ...
FROM MyTable1
UNION ALL
SELECT Col1, Col2, ...
FROM MyTable2
and further modify that so that, for columns that do not exist in that table, there was a conditional statement that either output "Col1" or "[Col1] = NULL"