sql-serverappendunion-all

How to 'Union all' two tables and the result to update the second table

I'm trying to UNION ALL (fully join both tables (they have the same column names)) two tables together but update the second table with the result of the union.

The code below gets the result I want but doesn't update table 2 with the result

SELECT * FROM table 2

UNION ALL

SELECT * FROM table 1

For example:

Table 2

Diagram name Column1 Column2 (tons more column names)
Name1 Data1 Data1
Name2 Data2 Data2
Name3 Data3 Data3

table 1

Diagram name Column1 Column2 (tons more column names)
Name4 Data4 Data4
Name5 Data5 Data5
Name6 Data6 Data6

End result (that I want table 2 to look like)

Diagram name Data1 Data2 (tons more column names)
Name1 Data1 Data1
Name2 Data2 Data2
Name3 Data3 Data3
Name4 Data4 Data4
Name5 Data5 Data5
Name6 Data6 Data6
Solution

  • Since union all doesn't remove duplicates, this would produce the same result:

    insert into table2 (diagram, col1, col2)
select diagram, col1, col2 
from table1 t1

    If you don't want duplicates, you could eliminate those with not exists:

    insert into table2 (diagram, col1, col2)
select diagram, col1, col2 
from table1 t1 
where not exists (
    select 1
    from table2 t2 
    where t1.diagram != t2.diagram and t1.col1 != t2.col1 and t1.col2 != t2.col2

)