Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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.

share|improve this question
2  
You should never use select * anyway. It is a SQl antipattern and causes the server to be inefficient. In a union, adding one column to one of the tables will break the query. –  HLGEM Mar 5 '12 at 16:31
1  
Yeah, specify the column list explicitly. Never use *. –  Remus Rusanu Mar 5 '12 at 17:05
    
@HLGEM: "never use select *" -- You've more recently said it was OK for "quick ad hoc query". In the interest of fairness, here's my (current) position on the matter –  onedaywhen Mar 6 '12 at 10:04

2 Answers 2

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)
share|improve this answer

In Standard SQL (removing the INTO clause):

SELECT *
FROM Table2
UNION ALL CORRESPONDING
SELECT * FROM Table3;

Sadly, SQL Server has not yet implemented CORRESPONDING qualifier (same applies to INTERSECT CORRSPONDING and EXCEPT CORRESPONDING).

share|improve this answer
    
actually SQl Server 2008 has Except and Intersect –  HLGEM Mar 5 '12 at 16:31
    
@HLGEM: I meant that SQL Server has not implemented INTERSECT CORRSPONDING and EXCEPT CORRESPONDING. I've edited to hopefully make this clearer. –  onedaywhen Mar 6 '12 at 9:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.