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!

Answer 1

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?