Union 50+ tables with different number of columns using SQL Server

Question

I have over 50 different tables which I would like to combine into one big table. All the tables have a different number of columns.

Currently, to union the tables together, I am writing an individual select statement for each of the tables, and inserting a null column if that column doesn't exist in the table. Then I am using UNION ALL to union them together.

For example:

(
select col1
        , null as col2 
        , col3
 from   table1

union all

 select col1
       , col2
       , null as col
from   table2
)

Although this works, it is very manual and time consuming. Is there a better, more efficient way to union these tables into one? As with over 50 tables, I am going to have thousands of lines of code.

Thank you!

Possible [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). What are you trying to accomplish? Surely, you are not stitching together some unrelated table schemas just for fun. — Ruud Helderman, May 09 '16 at 09:01
Oh sorry. The tables are all related and they refer to the same data, but not all columns are in all tables due to the way the original data was extracted. I am trying to create one big table which appends all tables into 1 so that I can query the data on the whole. However, I can't just select certain columns because there are some columns missing from certain tables which I actually need to query. — a123z, May 09 '16 at 09:11
Is this a one off query? Once you have all the data, in one table, will you need to run again? — David Rushton, May 09 '16 at 10:02
It is just a one off query, there should be no need to re-run again. — a123z, May 09 '16 at 11:36

score 0 · Accepted Answer · edited May 23 '17 at 10:28

You can query SQL Server metadata, and from the result dynamically construct a SQL statement. This can be done in any programming language, including T-SQL itself.

Here's a rough example; execute this query, copy/paste the result back into the query window, and execute that.

If the 50 tables have similar names (e.g. all start with Foo), then you can replace the exhaustive table list (WHERE TABLE_NAME IN ('table1', 'table2', 'table3') in my example) by WHERE TABLE_NAME LIKE 'Foo%'.

WITH
    AllTables (TABLE_NAME) AS (
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME IN ('table1', 'table2', 'table3')
    ),
    TablesWithSelectors (TABLE_NAME, COLUMN_NAME, Selector) AS (
        SELECT t.TABLE_NAME, a.COLUMN_NAME, CASE WHEN b.COLUMN_NAME IS NULL THEN 'NULL AS ' ELSE '' END + a.COLUMN_NAME
        FROM AllTables t
        CROSS JOIN (SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM AllTables)) a
        LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS b ON b.TABLE_NAME = t.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME
    ),
    SelectStatements (Sql) AS (
        SELECT
            'SELECT ' +
            STUFF((
                SELECT ', ' + Selector
                FROM TablesWithSelectors
                WHERE TABLE_NAME = r.TABLE_NAME
                FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
            , 1, 2, '') +
            ' FROM ' +
            TABLE_NAME
        FROM TablesWithSelectors r
        GROUP BY TABLE_NAME
    )
SELECT STUFF((
        SELECT ' UNION ALL ' + sql
        FROM SelectStatements
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'), 1, 11, '')

Thanks to: How to use GROUP BY to concatenate strings in SQL Server?

FYC, I refined the query, adding a final concatenation step. Now the result is really the finished query. — Ruud Helderman, May 09 '16 at 11:49

Union 50+ tables with different number of columns using SQL Server

1 Answers1