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

Question

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 | Coloumn1 | Coloumn2 | (tons more column names)

Name1 | Data1 | Data1

Name2 | Data2 | Data2

Name3 | Data3 | Data3

table 1

Diagram name | Coloumn1 | Coloumn2 | (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

Why are you trying to use a UNION ALL? Why not just insert to table 2 from table 1?? — Tab Alleman, Aug 15 '18 at 17:56

score 4 · Accepted Answer · answered Aug 15 '18 at 17:20

4

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

)

answered Aug 15 '18 at 17:20

sgeddes

62,311
6
61
83

How could i add this if there are like a 100 column headers is there a * function? – Sam Aug 15 '18 at 17:27
@ChrisBrook -- You might be able to run `insert into table2 select * from table1`. Depends on your db design (for example this won't work if you have an `identity` column). – sgeddes Aug 15 '18 at 17:27

score 2 · Answer 2 · answered Aug 15 '18 at 17:19

I think you just want to insert the records from table 1 into table 2:

INSERT INTO table2 ([Diagram name], [Data1], [Data2])
SELECT [Diagram name], [Data1], [Data2])
FROM table1;

Note that your current query does produce the intermediate result you want. If you wanted to populate/create a new table using that query, it would work. But the issue is that table 2 already has data in it.

score 2 · Answer 3 · answered Aug 15 '18 at 17:19

2

If you want to update table 2 with union all of table 1 and table 2, isn't it the same as inserting rows from table 1 to table 2?

Insert into table 2
select [Diagram name], [Data1], [Data2] from table 1

answered Aug 15 '18 at 17:19

Pm Duda

741
5
16

score 1 · Answer 4 · answered Aug 15 '18 at 17:44

If you don't want duplicates, you could eliminate those with not exists (same if there are like a 100 column headers), condition: table1 and table2 must have same columns count and same type of columns:

insert into table2
select distinct * from table1
except
select * from table2

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

table 2

table 1

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

4 Answers4