Sql-server – Perform UNION ALL regardless of column order in the two joined tables

sql serverunion

SELECT * INTO TABLE1 
FROM Table2
UNION ALL 
SELECT * FROM Table3;
GO

I am using this query to stack two tables together into one table. These tables should have the columns in the exact same order for this query to execute successfully.

I am wondering if there is a trick we can do so that it works regardless of the column order. Is there a way to instruct SQL Server to automatically line up the columns from both tables by name and perform the UNION?

In SAS, the trick is to write a OUTER UNION CORR query, for example.

Best Answer

I think you have two options. Either type out the columns in your query or do something with dynamic SQL.

declare @sql nvarchar(max)
declare @col nvarchar(max)

select @col = stuff((select ','+quotename(C.COLUMN_NAME)
                     from INFORMATION_SCHEMA.COLUMNS as C
                     where C.TABLE_NAME = 'Table2'
                     order by C.ORDINAL_POSITION
                     for xml path(''), type).value('.',  'nvarchar(max)'), 1, 1, '')

set @sql = 'select '+@col+' into Table1 from Table2 union all '+
           'select '+@col+' from Table3'

exec (@sql)
Related Question