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

i have on 50 different tables combine 1 big table. tables have different number of columns.

currently, union tables together, writing individual select statement each of tables, , inserting null column if column doesn't exist in table. using union union them together.

for example:

( select col1         , null col2          , col3    table1  union   select col1        , col2        , null col   table2 ) 

although works, manual , time consuming. there better, more efficient way union these tables one? on 50 tables, going have thousands of lines of code.

thank you!

you can query sql server metadata, , result dynamically construct sql statement. can done in programming language, including t-sql itself.

here's rough example; execute query, copy/paste result query window, , execute that.

if 50 tables have similar names (e.g. start foo), can replace exhaustive table list (where table_name in ('table1', 'table2', 'table3') in example) where table_name 'foo%'.

with     alltables (table_name) (         select table_name         information_schema.tables         table_name in ('table1', 'table2', 'table3')     ),     tableswithselectors (table_name, column_name, selector) (         select t.table_name, a.column_name, case when b.column_name null 'null ' else '' end + a.column_name         alltables t         cross join (select distinct column_name information_schema.columns table_name in (select table_name alltables))         left outer join information_schema.columns b on b.table_name = t.table_name , b.column_name = a.column_name     ),     selectstatements (sql) (         select             'select ' +             stuff((                 select ', ' + selector                 tableswithselectors                 table_name = r.table_name                 xml path(''),type).value('(./text())[1]','varchar(max)')             , 1, 2, '') +             ' ' +             table_name         tableswithselectors r         group table_name     ) select stuff((         select ' union ' + sql         selectstatements         xml path(''),type).value('(./text())[1]','varchar(max)'), 1, 11, '') 

thanks to: how use group concatenate strings in sql server?