5
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.

  • 3
    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
4

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

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 CORRESPONDING and EXCEPT CORRESPONDING).


Fully utilising standard SQL (and dropping what I trust is redundant ALL), simply:

TABLE Table2 UNION CORRESPONDING TABLE Table3
| 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
  • For those wondering, wtf, dba.stackexchange.com/q/212266/2639 – Evan Carroll Jul 16 '18 at 3:39
  • 1
    @EvanCarroll Good point about TABLE, I've updated my answer. However, I don't agree your point about 'detriments' of NATURAL JOIN. – onedaywhen Jul 16 '18 at 13:08
  • 1
    When you added name to your customer table instead of customer_name, were you guilty of saving a few key strokes? :) Seriously, if there is a good reason for creating duplicate names in the same schema, I haven't heard it so please let me know. Bottom line: if you design a schema with unique data element names and defend yourself against breaking changes then NATURAL JOIN, UNION CORRESPONDING, etc will work just fine; if you don't then they won't. No argument here. – onedaywhen Jul 16 '18 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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