Union between nonmatching columns tables

Question

Suppose we have 3 tables as follows:

Table1: Column1, Column2, Column3

Table2: Column1, Column2, Columnxyz, Column3

Table3: Column1, Column2, Column3

I have a view with the following DDL:

CREATE VIEW [dbo].[View1]
  AS (
    SELECT 'Table1' as [Source], * FROM [Table1] 
    UNION
    SELECT 'Table2' as [Source], * FROM [Table2]
    UNION
    SELECT 'Table3' as [Source], * FROM [Table3]
  );

That view works flawlessly assuming all tables have the same columns.

however, in the example above, with table2 having additional column: columnxyz What would I need to change in the view union? In other words, do I have to create Table1 and Table3 with this additional column as well, or can we circumvent this by somehow dynamically appending this extra column in the view to the other tables missing it and filling it with "None" or "N/A" as default data (since Table1 and table2 don't originally have this columnxyz so there's really no data anyways)?

Either what @DaleK said, or don't select that column, just the common columns. And you probably want union all not union — Charlieface
@DaleK i see so like: SELECT 'Table1' as [Source], [columnxyz] * FROM [Table1] bt what do you mean i should list all the columns individually? is it because of the order? — Cataster
@Charlieface thing is we need columnxyz since itll be fed to an SSAS cube (View1 will act as datasource) — Cataster
@DaleK yes i think i know what you mean, but for that problem, i just remember to recreate the view — Cataster
@DaleK maybe i actually have to do it here because columnxy as to be in a certain order anyways (e.g. the 3rd column) — Cataster

Dale K Dale K · Accepted Answer · 2021-01-22T01:52:58

Select a constant from the tables without the additional column, exactly the same as you are already doing for [Source]. However you will have to list the columns explicitly since the additional column is not first or last. As a best practice you should always list your columns anyway.

CREATE VIEW [dbo].[View1]
AS (
  SELECT 'Table1' as [Source], Column1, Column2, 'N/A' AS ColumnXYZ, Column3
  FROM [Table1] 
  UNION ALL
  SELECT 'Table2' as [Source], Column1, Column2, ColumnXYZ, Column3
  FROM [Table2]
  UNION ALL
  SELECT 'Table3' as [Source], Column1, Column2, 'N/A' AS ColumnXYZ, Column3
  FROM [Table3]
);

Note, I've used UNION ALL because UNION will attempt to-duplicate your data, which normally you don't want, and it suffers a performance penalty for doing so. Personally I always handle an de-duplication/grouping in other ways.

Union between nonmatching columns tables

1 Answers